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»»

Improve SIMPLE SELECT to retun 2 LAC Rows faster? Expand / Collapse
Author
Message
Posted Tuesday, December 17, 2013 7:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 7:35 AM
Points: 55, Visits: 83
Hi Team,

We have a Store Procedure and in this SP we are creating a TEMPORARY Table which has more than 200000 rows with 30 columns (2 of datetime, 4 of INT, 5 of [numeric](20, 3) and rest of VARCHAR(500) data types. This SP is giving the SIMPLE SELECT of this TEMPORARY Table in final output.

Now the problem is the final SELECT statement is taking more than 3 Minutes to list all records.

We tried by adding an Identity column to this Temp. Table and added Non-Clustered Index on this Identity column and in final output we don't include this column in SELECT statement but, in ORDER BY clause only. But, still it is taking more than 2.30 Minutes.

We need a solution to get this "SELECT" statement output faster.

Please advice.
Post #1523668
Posted Tuesday, December 17, 2013 7:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 13,110, Visits: 11,939
We can help you but you need to provide way more information for us first. Please see this article about best practices when posting performance issues. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523671
Posted Tuesday, December 17, 2013 10:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 7:35 AM
Points: 55, Visits: 83
HI! As per my understanding posted almost all info. about the problem. Please let me know what exactly you required to solve this problem. I am really eager to have the solution :).

Also please note that I took the SP in SSMS and executed individual queries and found that the Final SIMPLE SELECT statement is taking more than 3 Minutes to list all records.

Please advice.
Post #1523800
Posted Tuesday, December 17, 2013 10:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 13,110, Visits: 11,939
bharat sethi (12/17/2013)
HI! As per my understanding posted almost all info. about the problem. Please let me know what exactly you required to solve this problem. I am really eager to have the solution :).

Also please note that I took the SP in SSMS and executed individual queries and found that the Final SIMPLE SELECT statement is taking more than 3 Minutes to list all records.

Please advice.


I assume that you didn't bother to read the article I suggested. Ask yourself if you honestly think you would be able to provide anything resembling solid advice based on what you posted. I can't see your screen, I have no idea what your tables and indexes are like, I have no idea what the actual query you are running looks like, I have no idea how many rows are in the tables, I have no idea if your stats are current, to summarize, I have no idea how to help you.

At the absolute bare minimum you need to post an actual execution plan. Including ddl for the tables and indexes would be a big help too.

From your brief description of what you are doing I would ask why you need to copy rows from a table into a temp table to simply then select those rows back out. But without more details about what you are actually doing I can't really guess on that either.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523804
Posted Tuesday, December 17, 2013 10:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 7:35 AM
Points: 55, Visits: 83
Hi!

This SP has
an initial query to load data in temp. table with 5 tables joins. This query executed within 15 seconds.

Then have 7 update queries to update diff. diff. columns of this Temp. table from diff. diff. tables.
Each of these updates executed within 5-10 seconds.

Then we have the SIMPLE SELECT statement to return all the rows (207958) of this TEMP. table.

Temp. table don't have Indexes. That's why tried to add Identity column and Non-Clustered Index on it and
in execution plan of this SIMPLE SELECT statement SQL is doing TABLE SCAN.
Will attach the snap of Execution Plan soon.
Post #1523812
Posted Tuesday, December 17, 2013 10:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 13,110, Visits: 11,939
bharat sethi (12/17/2013)
Hi!

This SP has
an initial query to load data in temp. table with 5 tables joins. This query executed within 15 seconds.

Then have 7 update queries to update diff. diff. columns of this Temp. table from diff. diff. tables.
Each of these updates executed within 5-10 seconds.

Then we have the SIMPLE SELECT statement to return all the rows (207958) of this TEMP. table.

Temp. table don't have Indexes. That's why tried to add Identity column and Non-Clustered Index on it and
in execution plan of this SIMPLE SELECT statement SQL is doing TABLE SCAN.
Will attach the snap of Execution Plan soon.


Of course it is a table scan. You are selecting all the rows from it. Remember that you are first writing and retrieving 207958 rows of data. Most of the time being spent here is most likely disc I/O.


Temp. table don't have Indexes.


You could add them but I don't think it will help in this case because you are selecting all the rows so an index isn't going to help here.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523822
Posted Wednesday, December 18, 2013 5:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 7:35 AM
Points: 55, Visits: 83
Hi! Then how do we improve it?
Post #1524069
Posted Wednesday, December 18, 2013 6:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
bharat sethi (12/18/2013)
Hi! Then how do we improve it?


if you are selecting all rows(we still didn't see the query, which might be improved), then you can't speed it up with indexing;

so it comes down to amount of data returned, and the underlying hardware.

do you really need to return 207958 rows of data?

if it's being used to populate a web page, the data may be returned very quickly, but the creation and rendering of all the TD / TR tags takes any browser a TON of time to build a page...you need to identify where they "slowness" is occurring.


can you change it so it provides an intelligent summary of the information, instead of all the data? what are you doing with the data?

do you really need every column in the query? are you returning image/text/varchar(max) fields? are they even being used if they are returned?

is the slowness on the LAN, or over the internet? is your LAN gigibit speed?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1524081
Posted Wednesday, December 18, 2013 7:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 7:35 AM
Points: 55, Visits: 83
Hi! The data is not going to a web page. It's a SSIS package which process this task and then provide this data to sucessor tasks or other processes. there is no image/text columns (pl. refer previous replies for columns list) and H/w had a gigbit LAN only.

The query is Select <column names> from <Temp.TableName>.
Post #1524123
Posted Wednesday, December 18, 2013 7:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 13,110, Visits: 11,939
Try this link. It will be a great starting point for helping us find you an answer.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1524130
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse