Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Select is Slow Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 8:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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 #1522560
Posted Thursday, December 12, 2013 11:42 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:33 AM
Points: 238, Visits: 370
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
Post #1522581
Posted Thursday, December 12, 2013 11:46 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 11:20 AM
Points: 92, Visits: 2,482
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.
Post #1522582
Posted Friday, December 13, 2013 12:10 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 781, Visits: 1,390
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.
Post #1522586
Posted Friday, December 13, 2013 2:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Post #1522604
Posted Friday, December 13, 2013 3:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1522615
Posted Friday, December 13, 2013 6:07 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 4,246, Visits: 3,686
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
Post #1522646
Posted Saturday, December 14, 2013 8:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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.

Post #1522950
Posted Sunday, December 15, 2013 7:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #1523033
Posted Sunday, December 15, 2013 9:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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 )
Post #1523046
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse