Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select is Slow


Select is Slow

Author
Message
yuvipoy
yuvipoy
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 1316
Having a table with 100 columns and 1 million records.

Select Count(*) from Mytable;
COUNT(*)
----------
1001920
Time taken to return is 15 sec
select * from Mytable;
Time taken to return is 02:15 min
select * from Mytable order by some_coumn desc;
Time taken to return is 04:58 min
There is no primary key for the column which is ordered by
Order by is taking more time.
I need to show to client some 1000 records which are order by then filter condition

Conditions:
Need to Order by my data based on sort condition client specified
Then apply filter condition

sample

Select * from mytable order by some_column and Where condition like 1 to 1000 and 1001 to 2000 and so on.....

Note there are no Joins just straight select with one primary key
How to improve the query ?
MJ-1115929
MJ-1115929
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 469
You have to go for table level partition (split the tables in different disks), before that check the fragmentation level of the tables and defrag the same.

Is this table used as an archive or a regular use , If its regular use consider the table partition, also create a job for re-indexing in the low usage time.

Thanks & regards
MJ
Ville-Pekka Vahteala
Ville-Pekka Vahteala
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 3037
By how many of those hundred columns client wants to order? If number is limited then adding clustered primary key and index to each column helps.
Is there lot of changes in table, additional indexes might slow these.
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2597
is this table consist of all field with varchar/nvarchar, or there are multiple data types. i am asking this because if you have all varchar field it is a issue in itself. if there are varchar(max)/nvarchar(max) fields then you need to rethink.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8972 Visits: 19022
yuvipoy (12/12/2013)
Having a table with 100 columns and 1 million records.

Select Count(*) from Mytable;
COUNT(*)
----------
1001920
Time taken to return is 15 sec
select * from Mytable;
Time taken to return is 02:15 min
select * from Mytable order by some_coumn desc;
Time taken to return is 04:58 min
There is no primary key for the column which is ordered by
Order by is taking more time.
I need to show to client some 1000 records which are order by then filter condition

Conditions:
Need to Order by my data based on sort condition client specified
Then apply filter condition

sample

Select * from mytable order by some_column and Where condition like 1 to 1000 and 1001 to 2000 and so on.....

Note there are no Joins just straight select with one primary key
How to improve the query ?


Post the ddl for the table and the indexes. Post a few rows of sample data too if you can.
One million rows is trivial, the count would normally return within a second or so.
The timing for "select * from Mytable" is meaningless, most of the time will be network latency and screen refresh.

This doesn't make sense - "Select * from mytable order by some_column and Where condition like 1 to 1000 and 1001 to 2000 and so on....." - what are you trying to do here?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17592 Visits: 32265
To understand why a SELECT statement is slow, you need to look at the execution plan to understand how the optimizer is resolving the query. Also, you need to look to the system to see if you are experiencing blocking or resource contention.

Based on what you've posted, that's all I've got.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10264 Visits: 9571
After you address the stuff above about some of the specifics, here's a good article on catch-all queries http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ written by Gail Shaw. However, it sounds like you don't really know what you want yet.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
yuvipoy
yuvipoy
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 1316
the table consists of numbers and floats only.. int bigint , float data types only...
100 columns combination of above.
option will be given to user , where he can scroll through the records of 1 million rows .
* he might order by say column 10 desc
* so i need to do desc on column 10 and given the 1 million records based on it.
* user may choose any column to sort
* there may be multiple columns also to sort the data.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
yuvipoy (12/14/2013)
...where he can scroll through the records of 1 million rows... .


[sarcasm ON]
If the company can afford to pay someone to scroll through one million rows, they'll definitely have the time to way a few minutes each time the sort order is changed...
[sarcasm OFF]

More honestly: I'd question the business need to return a million rows in the first place. The time it takes to transfer the data will be significant.
Example: let's assume all 100 columns are defined as integer. Then one row will use 400byte. So you're transferring almost 400MB each time the sort order is changed.
Assuming the hardware hosting the SQL Server does provide a large enough amount of memory, data might be cached in memory leading to a faster return of the rows in a different order.
But again: What's the purpose of it?



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
yuvipoy
yuvipoy
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 1316
here i meant scroll is where condition..... for every scroll i will be giving 1000 records...
For the 1st time i will return 1000 records and next time 1001 to 2000 on 1 million rows which is sorted (order by )
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search