SQL Query

I have a SQL Query that takes some time to return answer.  Is there a way to speed up the response?
The table has about 5,000,000 rows of data.

SELECT vol FROM tblVolDelta13_18 WHERE [deltaId] = '50' and [loadTime] = '25200' and [expiryId] = '2' and [loadDate] = '40623'

Generally, the idea is to retreive the vol data for the latest date.  Running this query does take about 30 seconds to retreive the answer.

Thanks,
blueteamAsked:
Who is Participating?
 
Roman GhermanConnect With a Mentor Senior Software EngineerCommented:
Hi,
You're doing checking for a string, why is that?
I think that those columns which you're filtering by are integers, aren't they?

I think the code should look like this:
SELECT vol FROM tblVolDelta13_18 WHERE [deltaId] = 50 and [loadTime] = 25200 and [expiryId] = 2 and [loadDate] = 40623

Open in new window


Plus try adding indexes where possible.
0
 
Ephraim WangoyaConnect With a Mentor Commented:

It may just be a case of indexes

Are all the fields in your where clause indexed
0
 
Mohamed AbowardaSoftware EngineerCommented:
Check the database columns data type, probably you are using varchar instead of int, you should also update the query and remove the quotations to query about integers.
0
 
PagodNaUtakCommented:
Or you can use paging for this scenario.

Have a look here:

http://forums.devx.com/showthread.php?t=155210
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.