|
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
|
|
|
|
|
I didnt get that???
Gautham
|
|
|
|
|
i mean that in a database there are three table
emp1
emp2
emp3
all table have a column name id
how to count number of table with column id
|
|
|
|
|
select * from sysobjects t1 join syscolumns t2 on t1.id=t2.id where t2.name="columnname"
i guess, not sure though!
Gautham
|
|
|
|
|
This will show all tables where there is a column named id
SELECT TABLE_NAME
FROM YourDataBase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Id';
Here you are counting how many such cases are in your db.
SELECT COUNT(COLUMN_NAME)
FROM YourDataBase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Id' ;
Note: This is correct for SQL 2005
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
Hi all,
I'm using interopt object to handle data in an excel file, like that:
Excel.Application ExcelObj = new Excel.Application();<br />
<br />
Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(FilePath, 0, true, 5,<br />
"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null);<br />
<br />
Excel.Sheets sheets = theWorkbook.Worksheets;<br />
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(SheetIndex);<br />
<br />
Excel.Range range = worksheet.get_Range(BeginCol + BeginRow.ToString(), EndCol + BeginRow.ToString());<br />
System.Array myvalues = (System.Array)range.Cells.Value2;<br />
With cells formated as DateTime datatype, this method didn't get the correct format...Ex: 04/11/2007, it'll get as 39183.00...
How can I get the correct value? Please help me.
Thank you.
|
|
|
|
|
Hi i have doubt in roles using in vb.net...,
in my sqlserver for a paricular database,i created two application roles..and i given permission for those roles also..,
Now how to tell the roles in vb.net..,
(i.e) how to interact with those roles from vb.net codes...,
I dont know how to interact with those roles...,
so plz give me some idea..,if any sample codes will be there...,
it will be more usefull for me...,
Magi
|
|
|
|
|
i have one column datatype as datetime
i want to reaplce year of all date to 2030
can i use repalce and left functionbut its showing error plz help
|
|
|
|