|
|
Hi,
after reading the two, i came out with a new query that combines the two query together. However the speed is still not good. Still thinking of ways to make it faster.
In the query what i want is to get data out from 3 tables that is corresponded to B value in one of the table.
|
|
|
|
|
The following SQL would return all of the data from all 3 tables:
SELECT ppar_engparameters.parmval, ppar_engparameters.parmname,
prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag,
prcd.prodstatis, catg_pll.catnumber, catg_pll.category
FROM ppar_engparameters
INNER JOIN prcd
ON prcd.prcdname = ppar_engparameters.prcdname
AND prcd.activeflag = 'A'
LEFT OUTER JOIN catg_pll
ON catg_pll.partprcdname = prcd.prcdname
AND catg_pll.partprcdversion = prcd.prcdversion
WHERE ppar_engparameters.parmval= 'MyValue'I have removed the "distinct" clause that you had in the original. This is often used to hide poorly-designed tables - you understand where-and-why you are getting duplicates before blindly using this.
Proper indexes are required to make this run fast. I would expect the following indexes (or primary keys) from your original description:
--For the link from product-parameter to product.
create unique index prcd_idx1 on prcd (prcdname, prcdversion)
--For the link from product.
create index catg_pll_idx1 on catg_pll (partprcdname, partprcdversion)
--For the search on parameter values.
create index ppar_engparameters_idx1 on ppar_engparameters
(parmval, activeflag)Other indexes may also be required for other activities. There should ideally be a primary key or unique-index on every table.
Oracle's query optimiser uses statistical data from indexes to determine the best way of answering your queries. There is a command (that I cannot remember) for recalculating these statistics.
Oracle is also able to display the query-plan that it uses - so you would be able to tell exactly what Oracle is doing under the covers. Search Google[^] for more information.
Hope that helps.
Regards
Andy
|
|
|
|
|
The distinct clause does make a difference of ard 20s. But how come it give repeated values?
My current code is
select distinct ppar_engparameters.parmval, ppar_engparameters.parmname,<br />
prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag, prcd.prodstatus, <br />
catg_pll.catgnumber, catg_pll.category<br />
from ppar_engparameters<br />
inner join prcd<br />
on prcd.prcdname = ppar_engparameters.prcdname and prcd.activeflag='A'<br />
inner join catg_pll<br />
on catg_pll.partprcdname = ppar_engparameters.prcdname and catg_pll.partprcdversion = <br />
ppar_engparameters.prcdversion<br />
where ppar_engparameters.parmval='MyValue'
As for the query plan and stuffs i still cant access to it.
Thanks.
Tony
|
|
|
|
|
Btw.. Is there anyway for ADO to return only certain column back into excel?
|
|
|
|
|
I've never used Oracle, so this may or may not help. But in SQL Server concatenation of strings will disable the use of any indexes. So your 2nd query might perform better if you rewrote it like this:
<br />
SELECT catnumber, category <br />
FROM catg_pll<br />
where catg_pll.partprcdname = prcd.prcdname AND catg_pll.partprcdversion = prcd.prcdversion <br />
|
|
|
|
|
Can I have tabbed reports ie (more than 1 report using on sp) on SQL reporting services 2000?
So it would look similar to more than 1 Excel spreadsheet.
Any help welcome...
Harvey
|
|
|
|
|
I'm working on a project which loads 8 reports at once. Each report queries the same data but just aggregates it differently. So what I had to do was 'preload' the data first, aggregate it and store the results (the aggregates not the actual data - too much) in a permenant table. Then I load the reports and they each just query the results table and display the reports (charts). The reason for this approach was that the query which was common to each report took at least 3 minutes to run and sometimes a lot more.
I haven't investigated what you're suggesting, and I'm using 2005. But I don't think it's possible as each report uses a separate connection and they don't load syncronously.
|
|
|
|
|
Thanks Mark, but I'm just running one query, say on sales in a year, I then want the report to split into 12 tabbed reports, one for each month. Is this possible in SQL reporting Services 2000, I have been told someone has seen it once !!
|
|
|
|
|
You can filter your dataset from your sproc after the fact for your table/matrix.
|
|
|
|
|
Hi,
Thanks for the help Mark,
Filter my dataset in reports designer you mean?
Harvey
|
|
|
|
|
Yep, on the tabled data region you should be able to set filters. Fine for smaller datasets.
If the dataset you are getting back from your sproc is large, I'd probably lean towards filtering it server side and getting one dataset for each month...
|
|
|
|
|
Hi, that works but if filter is set to January I only get back January data of course, someone claims to have seen 1 query produce 1 report but with 12 tabbed sections, much like an excel spreadsheet with 12 workbooks. I'm not sure this is possible.
Any one know ?
|
|
|
|
|
We have a VB6 app that uses ADO to connect to SQL Server. When the client leaves it running on her notebook (without any activity) for about an hour, the app freezes. I suppose it's due to the SQL connection timing out due to inactivity.
Has anyone seen this ?
Johan Lombaard
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former - Albert Einstein
|
|
|
|
|
Johan Lombaard wrote: Has anyone seen this ?
Nope. If the connection times out, the app would probably crash on an attempt to get to the database the next time.
|
|
|
|
|
First, the database connection should only be opened long enough to perform a query, and then shut down immediately afterwards.
Second, a sql connection should not close itself after a certain amount of inactivity (the calling app should always be in control). If it DOES shut itself down, and as a result causes your app to crash, it's your app's fault.
Second, "I suppose" is not acceptable. Run it through a debugger and find out why it's happening. If you have to, make it write a log file and just let it run until it freezes. At that point, you'll probably be able to at least narrow the problem down.
Not using the tools you have before asking that question is inappropriate.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
Hi all,
I'm trying to perform a sql statement that will accommodate both of the following cases, but I'm having some trouble...
Let say you have a field in you table called: SURNAME
and there are two surnames, namely: MCGOWAN and McGOWAN
Then I do my select statement select * from table where surname like upper('MCGOWAN')
But my select statement only returns one record. How can I change my select statement so that it can accommodate both surname situations?
Many thanks in advance
Regards,
The only programmers that are better that C programmers are those who code in 1's and 0's ![Green Alien | [Alien]](https://codeproject.freetls.fastly.net/script/Forums/Images/alien.gif)
 Programm3r
My Blog: ^_^
|
|
|
|
|
select * from table where upper(surname) like 'MCGOWAN'
|
|
|
|
|
|
Is it better to create a database on my PC and include it into the installation package or should I rather create the DB in code, when the application is run for the first time? It is a Windows desktop appliacation.
So far, I have included the DB into the installation and when there was an upgrade, only the new fields have been checked/added to the tables.
Now I was thinking if it would be a better idea to create the whole database in code at startup:
1) Check if the DB exists. If not, create it.
2) Check if all the tables exist. If not, create them.
3) Check if all the fields in all tables exist. If not, create them.
What do you think?
Thanks,
Petr
|
|
|
|
|
I've had the installer create the database from scripts in the past. It worked well for me.
|
|
|
|
|
Hi,
I have been used sql for my VC++. NET apps’ database. I want to make app that more than one user can use it from a shared hdd via network. E.g. let the app is on computer A, a hdd will be shared via network on computer A, and Computer B will use app via Computer A.
After configuring .NET Framework settings with CasPol.exe, app is working. But whenever it try to reach database, this error occurs: “The file” \\....” Is on a network path that is not supported for database files. An attempt to attach auto-named database for file “\\...” failed. A database with the same name exists, or specified file cannot be opened, or its located on UNC share…”.
Both of computers (A and B) have SQL Server 2005 Express Edition. At first i use Windows Auth. For login but after i had this error, i tried to make app use “Mix mode” and login as “sa 12345”. I changed Sys. Admin. Pass as “12345” and set “remote connections” settings to “TCP/IP”.
How can i solve this problem? Or how do the apps which has same strategy as this app work? Can you help me ? Thanks…
Erman
|
|
|
|
|
SQL Server, even Express edition, expects to have complete control of the file. It doesn't expect changes to be made from under it - it aggressively caches portions of the file so it doesn't need to read from the disk file, and makes changes in RAM and lazily writes them back to disk, to consolidate write operations (and thereby reduce the amount of writing it has to do). It explicitly doesn't support data files on the network as the network redirector introduces additional performance issues.
SQL Server does support opening files on a Storage Area Network, but the Logical Unit Number (virtual disk) that the files are on must be dedicated to that SQL Server, unless using SQL Server clustering.
If you need multiple clients to share the same database, you need a database server.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
If i attach mdf to Computer A's SQL Server manually then i make a constr for it without "|DataDirectory|" and Computer B runs exe via Computer A, will it work ?
|
|
|
|
|
Aloha,
All my rows have a column called "CM" with an int value. I would like to find the 3 rows where CM is as close as possible to CM in a specific row.
An example:
----
I have a row where CM is 120. I now want to find the 3 rows where CM is as close as possible to 120.
The 3 rows with the closest match would for example be 110, 125 and 130 if there are 10 rows and CM in those rows are: 80, 90, 100, 110, 120, 125, 130, 140, 150 and 160.
----
Anyone know how I can do that with a SQL SELECT?
Thanks, Mads
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
