| Author |
Topic |
|
bhaasjoshi
Starting Member
17 Posts |
 Posted - 2009-07-14 : 03:19:08
|
| Hi,I have a table which is around 2 lakh rows. A simple query such as the following takes more than a minute to execute. Please help me increase the performance of this query.SELECT a,b,c,d,e FROM table1WHERE b='something' AND d='SOMETHING'Best Regards,B |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-14 : 03:21:36
|
This is not possible with the given info.200,000 rows should not be a problem.Maybe your server is an old PC from 1980  Can you show the create of the table?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
bhaasjoshi
Starting Member
17 Posts |
Posted - 2009-07-14 : 03:41:47
|
| Thanks Fred,But I dont know much about the create of the table/or the server too :) but its just taking up too much time. Is there any way or logic out of this?B |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-14 : 03:48:52
|
Thats why I would like to see the create statement.Indexes on the table can speed up the performance.But without any information it is not possible to help.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
bhaasjoshi
Starting Member
17 Posts |
Posted - 2009-07-14 : 03:52:17
|
| Thought so. Unfortunately I dont have the right/access to see the CREATE of the table. So I guess I'm stuck then :( |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-14 : 03:57:34
|
If you don't have the right to see the create then it cannot be your job to speed up the database.Your Example Query is sooooo simple there is nothing wrong with it.Tell it to your dba.Fred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-14 : 04:26:58
|
| Hi webfredis it possible to check the index type..using this "EXEC sp_helpindex <Table_Name>"teach me if am in wrong..-------------------------R.. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-14 : 04:35:27
|
quote:
Originally posted by rajdaksha
Hi webfredis it possible to check the index type..using this "EXEC sp_helpindex <Table_Name>"teach me if am in wrong..-------------------------R..
Yes. You can. Check that column b and d is it indexed ?Are you querying a table or a view ?Also, how many rows is your query returning ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
bhaasjoshi
Starting Member
17 Posts |
Posted - 2009-07-14 : 04:43:48
|
| I'm querying a view, my query is expected to return around 9500 rows. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-14 : 04:48:15
|
can you post the view here ? looks like it's culprit.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
bhaasjoshi
Starting Member
17 Posts |
Posted - 2009-07-14 : 05:17:42
|
| I can't. The application (Integrated Review), I work on is specifically designed for the client and even if it wasn't I would not have done so. :) Hope you understand. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-14 : 05:34:20
|
Hey motor mechanic my car isn't running well, can you help me please?Maybe I can - show me your car...No sorry but I cannot show my car - hope you understand.Then I cannot help you - hope you understand.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
bhaasjoshi
Starting Member
17 Posts |
Posted - 2009-07-14 : 06:05:26
|
| Point Noted. But you can give directions to a place you've never been before, they might not be 100% accurate, but it could still help someone. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-14 : 06:14:38
|
try google or bing for articles on query optimization and indexing and check that you have appropriate indexesgood luck.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
Improve query performance for simple SELECT