|
Basically my output of the query should return something like this:<br />
(The numbers are average of some tests that were ran on differnt model and year of cars. In order to calculate these averages I need the joins both places.)<br />
<br />
______________|2000|2001|....|2006<br />
Avalon________|__12|__12|....|__11<br />
Camry_________|11.5|11.5|....|__10<br />
Celica________|__10|__10|....|__10<br />
Corolla_______|___9|___9|....|___7<br />
Highlander____|____|____|....|__10<br />
Land Cruiser__|__15|__14|....|__14<br />
<br />
It used to be faster when I had this query in 1 dimentional form:<br />
Avalon |2000| 12<br />
Avalon |2001| 12<br />
...<br />
Avalon |2006| 11<br />
Camry |2000|11.5<br />
...<br />
<br />
When I had the query result in 1D format I just changed to 2 dimentional format programatically (using 2D array and binary search). I figured it would be faster if I do the whole thing in one step within the sql query.<br />
<br />
Maybe I should just stick with the old version. I mean how do you guys make crosstab queries in MS SQL?<br />
|
|
|
|
|
Could I maybe see a little of your table structure and data? Not enough to give away any trade secrets...I just can't figure why this won't yield to a GROUP BY. Actually, you've piqued my puzzle-brain, and I won't be able to rest until I conclude with you "Yep; can't do it."
|
|
|
|
|
The structure itselft is not really complex.
A. Cars: Make, Model, CarID, Country, BodyType, etc
B. Tests: TestID, CardID, YearRan, etc
C. EngineTests: EngineTestID, TestID, etc
D. TestOutcomes: OutcomeID, EngineTestID, TestID, MyNumericValue, etc
A - B - C - D
"-" indicates one-to-many relationship
I need averages for selected cars and selected test years. Not a rocket science. It's fairly large database. You can think of the scale as current car manufacturers data.
As I said, it runs smooth if I do the select statement as 1 dimentional (by doing GROUP BY and turning it into 2D programmatically)
And I haven't been resting for about a week now.
|
|
|
|
|
This has the look of something that seriously needs to be converted into a data warehouse. Now that you are trying n-dimensional transformations, you really need to look at what SQL Server BI can do for you.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Is it smarter than the normal one? And does it cost more?
If it does I'll stick with my dumb one and break the query into smaller pieces and build the 2 dimentional table withing the program. Takes 30 seconds.
Thank you for all your help!
LESSON LEARNED: NO CROSSTAB QUERIES ON HUGE Databases!!!
Is that right?
|
|
|
|
|
If you've got SQL Server 2005, it's already present in there. It's also very powerful, and designed to solve issues like this.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I don't know if you could tolerate this in your app, but I rearranged the tables somewhat to reduce it to only 3 tables:
CREATE TABLE Cars (
CarID INTEGER IDENTITY PRIMARY KEY
, Make VARCHAR(20)
, Model INTEGER
, BodyType VARCHAR(20)
, Country VARCHAR(20)
);
INSERT INTO Cars(Make,Model)
VALUES('Camry',2000);
INSERT INTO Cars(Make,Model)
VALUES('Avalon',2000);
INSERT INTO Cars(Make,Model)
VALUES('Camry',2001);
INSERT INTO Cars(Make,Model)
VALUES('Avalon',2001);
INSERT INTO Cars(Make,Model)
VALUES('Camry',2002);
INSERT INTO Cars(Make,Model)
VALUES('Avalon',2002);
INSERT INTO Cars(Make,Model)
VALUES('Camry',2003);
INSERT INTO Cars(Make,Model)
VALUES('Avalon',2003);
INSERT INTO Cars(Make,Model)
VALUES('Camry',2004);
INSERT INTO Cars(Make,Model)
VALUES('Avalon',2004);
INSERT INTO Cars(Make,Model)
VALUES('Camry',2005);
INSERT INTO Cars(Make,Model)
VALUES('Avalon',2005);
CREATE TABLE TestTypes (
TestTypeID INTEGER IDENTITY PRIMARY KEY
, Name VARCHAR(50)
, Description VARCHAR(255)
, Measures VARCHAR(50)
, Units VARCHAR(20) --results are what? HorsePower, Seconds, ...
);
INSERT INTO TestTypes(Name,Description,Measures,Units)
VALUES('Engine Test/Efficiency', 'etc.', 'Fuel Efficiency', 'kg/hr');
INSERT INTO TestTypes(Name,Description,Measures,Units)
VALUES('Engine Test/Power', 'etc.', 'go-fast ability', 'hp');
INSERT INTO TestTypes(Name,Description,Measures,Units)
VALUES('Handling/Slolam', 'standard slolam setup', 'min time', 'seconds');
INSERT INTO TestTypes(Name,Description,Measures,Units)
VALUES('Quarter Mile', 'etc.', 'min time', 'seconds');
CREATE TABLE Tests (
TestID INTEGER IDENTITY PRIMARY KEY
, TestTypeID INTEGER
, CarID INTEGER
, YearRun INTEGER
, TrialNumber INTEGER
, Result SINGLE
);
Then I entered a bunch of "Quarter Mile" times like this:
INSERT INTO Tests(TestTypeID,CarID,YearRun,TrialNumber,Result)
VALUES( 4 , 1 , 2000 , 1 , 8);
plus too many more to list here...
Then ran the following:
SELECT D.Make AS Make
, (SELECT AVG(Result) FROM Tests WHERE CarID=D.ID2000 AND TestTypeID=4) AS 2000
, (SELECT AVG(Result) FROM Tests WHERE CarID=D.ID2001 AND TestTypeID=4) AS 2001
, (SELECT AVG(Result) FROM Tests WHERE CarID=D.ID2002 AND TestTypeID=4) AS 2002
, (SELECT AVG(Result) FROM Tests WHERE CarID=D.ID2003 AND TestTypeID=4) AS 2003
, (SELECT AVG(Result) FROM Tests WHERE CarID=D.ID2004 AND TestTypeID=4) AS 2004
FROM (SELECT DISTINCT C.Make
, (SELECT CarID FROM Cars WHERE Make=C.Make AND Model=2000) AS ID2000
, (SELECT CarID FROM Cars WHERE Make=C.Make AND Model=2001) AS ID2001
, (SELECT CarID FROM Cars WHERE Make=C.Make AND Model=2002) AS ID2002
, (SELECT CarID FROM Cars WHERE Make=C.Make AND Model=2003) AS ID2003
, (SELECT CarID FROM Cars WHERE Make=C.Make AND Model=2004) AS ID2004
FROM Cars AS C) AS D;
and got this output:
Make 2000 2001 2002 2003 2004
Avalon 9.5 11.5 13.5 9.5 11.5
Camry 8.5 10.5 12.5 8.5 10.5
This has no joins, and *I think* would run very fast since it's not re-looping over and over.
You could dump your existing data into the table structure I've described with some SELECT statements. You could also replace the hard-coded "AND TestTypeID=4" with "AND TestTypeID=@testIDnum" to get summaries of all the tests run.
Let me know if this works for you. If you want, I can send you the Tests table insert data I used.
ps:
I also did this query:
SELECT TT.name AS Test_Name, D.Make AS Make
, (SELECT AVG(Result) FROM Tests WHERE CarID=D.ID2000 AND TestTypeID=TT.TestTypeID) AS 2000
, (SELECT AVG(Result) FROM Tests WHERE CarID=D.ID2001 AND TestTypeID=TT.TestTypeID) AS 2001
, (SELECT AVG(Result) FROM Tests WHERE CarID=D.ID2002 AND TestTypeID=TT.TestTypeID) AS 2002
, (SELECT AVG(Result) FROM Tests WHERE CarID=D.ID2003 AND TestTypeID=TT.TestTypeID) AS 2003
, (SELECT AVG(Result) FROM Tests WHERE CarID=D.ID2004 AND TestTypeID=TT.TestTypeID) AS 2004
FROM (SELECT DISTINCT C.Make
, (SELECT CarID FROM Cars WHERE Make=C.Make AND Model=2000) AS ID2000
, (SELECT CarID FROM Cars WHERE Make=C.Make AND Model=2001) AS ID2001
, (SELECT CarID FROM Cars WHERE Make=C.Make AND Model=2002) AS ID2002
, (SELECT CarID FROM Cars WHERE Make=C.Make AND Model=2003) AS ID2003
, (SELECT CarID FROM Cars WHERE Make=C.Make AND Model=2004) AS ID2004
FROM Cars AS C) AS D, TestTypes AS TT
ORDER BY TT.name;
and got this result:
Test_Name Make 2000 2001 2002 2003 2004
Engine Test/Efficiency Camry
Engine Test/Efficiency Avalon
Engine Test/Power Camry
Engine Test/Power Avalon
Handling/Slolam Camry
Handling/Slolam Avalon
Quarter Mile Camry 8.5 10.5 12.5 8.5 10.5
Quarter Mile Avalon 9.5 11.5 13.5 9.5 11.5
the formatting looks all screwed up here because CodeProject removes all the spaces.
-- modified at 13:42 Friday 13th April, 2007
|
|
|
|
|
Thank you for you effort on this. You've spend lot of time on this.
I have lot of JOINs and conditions within each sub SELECT query. how many rows did you add to Tests?
When I run my query on a smaller scale (ex: Just Toyotas years 2000-2006) it works fine (max 1 minute wait). When I do something like all SUV's then it goes more than 3 minutes and nobody wants to wait for that long.
Thank you for everyone who tried to help! And here's my conclusion:
DO NOT RUN a CROSSTAB query like this if you have complex database and each table has thousands and thousands of rows and especially if one of the tables has more than 1 million rows. This kind of cross tabbing in MS SQL just WILL NOT WORK (fast enough)! I don't know about other database engines but it seems like the engine SHOULD optimize it for you because compilers such as modern C++ compilers optimize codes. In and IDEAL database engine using this CROSSTAB example it should break down the years into threads and run them seperately - it saves lot of times.
WHY? BECAUSE when I do this query using separately (dividing into smaller pieces by years) and if I run it it takes 10 seconds. Then I build the 2D table out of all this sub queries using any programming language - it takes another 10-20 seconds. So I'm going back to my old method.
It also brings up another question:
Can you do threads in MS SQL query and do some sort of wait on those? I figured it should do it in the background because all it takes is one semester worth of class with a project. I mean, the Microsoft operating systems are capable of doing threads (so it runs), so why they shouldn't integrate it into the SQL server? It's not a rocket science!
CORRECT ME IF I'M WRONG. I COULD BE!!!
|
|
|
|
|
You're welcome. But, I needed the practice anyway, and besides, once I got thinking about the problem, I couldn't get it off my mind. I'll probably used what I learned in some other project.
True, my Tests table only had 48 rows, but I don't believe that is the issue. If you carefully consider my query, you'll see that the DB engine only has to scan the Cars table once, and (assuming both Tests.CarID and Tests.TestTypeID are indexed) the Tests table ONCE! That is the key. I believe the reason yours is running slowly is due to the complexity of the table structure, not because you're attempting a crosstab.
If it's utterly impossible to simplify the table structure, or to dump just enough of the tables into a simplifed structure so this query will run quickly, then you may be right about not doing it this way.
Good Luck.
|
|
|
|
|
Yeah, I can see your query would run faster but my database is far more complicated than this example. But you're right! I have to look at the structure, indexes, etc, of my database and see if I can make this query look like yours. However, I don't think it can look like yours because it's very dynamic query and I don't want to create different queries for each cases - I'm just modifing parts of the query. Maybe I shouldn't be so lazy and write a dedicated queries for each of the cases (even though it's against the best programming practices).
... No wonder why there's so many database jobs out there =)
Thank you again!
-- modified at 16:47 Friday 13th April, 2007
|
|
|
|
|
I wanted to know if it is possible to get the data in parts.
I mean my application has many records in the table, so for a sql query like select * from table, there is lots of records to be read from the ResultSet.
Is there any query like
select (first 100) * from table
then
select (next 100) * from table
.
.
.
something like this ?
please help
|
|
|
|
|
select top 100 records from table where id > @id The first time in, pass in an @id of 0. Next time, pass in the maximum id of the previous results.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
You are right that it is a simplistic solution. I find that it fits a lot of cases though.
A lot of time when you sort/shuffle the id's around, this is done by retrieving the data in one go and performing the sorting/shuffling on the server.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
SELECT *<br />
FROM Table <br />
WHERE RowNumber Between @FirstRow AND @EndRow
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
I still didnt get what to do
How should the above command be written as ? my table name is 'orders' and there are only 2 fields in that 'ORDERID' and 'ITEMDESIRED'
There is no primary key as such. Yes I can make 'ORDERID' the Primary key though.
What should be the '@FirstRow' and '@EndRow' values be?? or should I write them just like that. Please help, its urgent.
I am using the ojdbc14.jar to connect to the Oracle 10g database.
I read somewhere about the LIMIT and OFFSET command, but tried it in Oracle SQL* Plus and saw that it does not work. Also it gave the same exception when tried to run the command through my Java program.
The command I ran is
<br />
select * from orders limit 10;<br />
and the Exception I got is
<br />
ORA-00933: SQL command not properly ended<br />
|
|
|
|
|
The example query I gave works fine for SQL Server 2005
@FirstRow = row, from which you want to start getting the results
@EndRow = last row of your results
In your case, for exapmple, it can be
SELECT OrderID,ItmDesired
FROM ORDERS
WHERE OrderID BETWEEN 5 AND 25
OR, if this does not work, in your db, try:
SELECT OrderID,ItmDesired
FROM ORDERS
WHERE OrderID > 5 AND ORDERID < 25
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
i problem
in a database i do i find how many feilds name id in database
|
|
|
|
|
You have another problem. The inability to read posting guidelines.
You've already posted about this, so don't start a new thread. It's not polite.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O'Hanlon wrote: another problem. The inability to read posting guidelines
|
|
|
|
|
I am using MS Access 2003 with VB.Net 2003. In one of my Table A field(ResignDate) is of type Text
I am writing following query to retrieve the name and other information of employee who resigned before a specific date which is to be given in the condition.....
my query is..
Dim sql16 As String = "SELECT Employee_AutoID,NameInitial,NameInitial+' '+Name " & _<br />
"AS Emp_Name,Designation FROM tblEmployeeMaster " & _<br />
"WHERE Resign_Flag='No' OR CDate(ResignDate)< #" & endDayOFFinancialYear & "# "
what is wrong in the query....
Tirtha
Miles to go before I sleep
|
|
|
|
|
remove Cdate function
resigndate='"+enddayofffinancialyear+"'
|
|
|
|
|
how do we count stored proc in database
how do we count system difined stored proc and userdifined stored proc
|
|
|
|
|
The stored procedures in sql are objects so whenever a stored procedure is created its information is stored in the system table sysobjects, and as it is a sp the type would be 'p'
Thus to know the number of stored procedures for a database the query would be
select count(*) from sysobjects where type='p'
Gautham
|
|
|
|
|
thanks but now i have another problem
in a database i do i find how many feilds name=id in database
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
