Input Query - Please check..

Question

Post reply

Input Query - Please check..

DBRookie

SSC Veteran

Points: 281
More actions
December 3, 2010 at 6:42 am

#143752

This is my first time to do database so I'm not sure if I'm sending the correct form of query. I'm thinking that this contribute to my timeout error. Note that the error does not occur all the time.
I also notice that I'm getting a series of this error in the morning.
-------------------------------------------
SELECT TYPE, PN, MACHINE, TDATE, SN, PF
FROM TESTDATA
WHERE TDATE >= '12/3/2010 10:00:00 AM'
AND MACHINE = '2792BA - HS'
AND TYPE IN ('AUDIT', 'TEST')
ORDER BY TDATE DESC
-------------------------------------------
Please advice..
Thank you very much!
dbrookie

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

I don't see anything wrong with this query.

Questions:

1. What does the actual execution plan show?

2. How many records are in this table?

3. What indexes do you have on this table, and which one (if any) is clustered?

You might want to look at the article linked to in my signature for how to post performance related problems.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

DBRookie SSC Veteran Points: 281 More actions · Answer 2

WayneS (12/3/2010)
I don't see anything wrong with this query.
Questions:
1. What does the actual execution plan show?
2. How many records are in this table?
3. What indexes do you have on this table, and which one (if any) is clustered?
You might want to look at the article linked to in my signature for how to post performance related problems.

I can only come up with execution plan (please see attachment). Last time I checked the table contains 891648 rows/12 column. Rows will populate as we keep data from the beginning of the year.

Random question:

* If I'm able to resolve the timeout error, we would like to use our application (written in LabView) to a multiple PC. What it does is, it pulls data from the server, process the data, and display it in LED screen (like production display). Will I be creating the same timeout error if I run the application at the same time?

WayneS SSC Guru Points: 95460 More actions · Answer 3

Well, the execution plan that you sent is just doing a "SELECT * FROM TestData". This results in a simple table scan of the TestData table. Which only tells me that there isn't a clustered index on this table. If you trying to pump nearly 1 million rows to your client, yeah, I would expect that to time out.

However, this is not the query that you said you were running. Can you get the execution plan for that?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

DBRookie SSC Veteran Points: 281 More actions · Answer 4

WayneS (12/3/2010)
Well, the execution plan that you sent is just doing a "SELECT * FROM TestData". This results in a simple table scan of the TestData table. Which only tells me that there isn't a clustered index on this table. If you trying to pump nearly 1 million rows to your client, yeah, I would expect that to time out.
However, this is not the query that you said you were running. Can you get the execution plan for that?

Here's the new execution plan with the actual query. You're right, I'm pulling these data from nearly 1 million rows though I'm only sending current date data. I think regardless of the date I specified it will return a timeout error due to the its large number of rows. What is the best solution for this and how?

Thank you very much!

dbrookie