April 27, 2015 at 11:40 am
Data is used by a some other application which i have no idea about.
I dont think it is one by one operation.
April 27, 2015 at 12:14 pm
neeraj-344433 (4/27/2015)
Will it help if I clustered index on the table by adding a identity column to it. As of now, its a heap because there is no index and we are doing the select * without any filter.Just a thought?
I won't risk being repetitive. I will come right out and do it. If you want help you need to give us information. I gave you a link in my first response that explains what is needed to help with performance. It is obvious you did not read that. The real issue here is that you are not providing enough information and are ignoring those who are offering to help.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 27, 2015 at 12:22 pm
And I can't see any way to speed up a query that always returns 100% of the data (columns and rows). Well, you could put the database on SSD drives and use a 10GB network to transfer the data, but not sure if that is realistic in this case.
April 27, 2015 at 12:46 pm
@sean Lange.
I certainly have no intention to ignore anybody who is trying to help me. If thats the message being conveyed, my apologies.
So lets start from beginning (given i have read the article you provided this time.)
We have a process which runs every night which extracts data from SQL server and performs the indexing exercise on a unix box using that data. Below are the steps in which this process works
1. A stored procedure is executed and it truncate and loads the data in staging table (stg_IndexDetails)
2. The staging table has 85 columns and about 3 million records which are loaded every night as part of the above mentioned truncate and load process.
3. Then this table is queried as (Select * from stg_IndexDetails (NOLOCK)) and using the result set of the query, the indexes are generated on the other side.
4. This is not an one by one operation as process captures the whole resultset and then loops through it. This is a third party application so no control over it about how it processes this data.
My problem is the performance of Select * from stg_IndexDetails query (step 3). This alone takes about 2 hours to return all the data.
Does this help?
Thanks again!
April 27, 2015 at 1:12 pm
neeraj-344433 (4/27/2015)
@Sean Lange.I certainly have no intention to ignore anybody who is trying to help me. If thats the message being conveyed, my apologies.
So lets start from beginning (given i have read the article you provided this time.)
We have a process which runs every night which extracts data from SQL server and performs the indexing exercise on a unix box using that data. Below are the steps in which this process works
1. A stored procedure is executed and it truncate and loads the data in staging table (stg_IndexDetails)
2. The staging table has 85 columns and about 3 million records which are loaded every night as part of the above mentioned truncate and load process.
3. Then this table is queried as (Select * from stg_IndexDetails (NOLOCK)) and using the result set of the query, the indexes are generated on the other side.
4. This is not an one by one operation as process captures the whole resultset and then loops through it. This is a third party application so no control over it about how it processes this data.
My problem is the performance of Select * from stg_IndexDetails query (step 3). This alone takes about 2 hours to return all the data.
Does this help?
Thanks again!
I have asked just like the article for the table and index definition which we haven't seen...although it sounds like you are just selecting 3 million rows so not sure it is needed. Do you really need to truncate and reload all 3 million rows every single day? That seems horribly inefficient to me.
If you are truncating that table and are the only process using this stg_IndexDetails table why the NOLOCK hint?
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 27, 2015 at 1:14 pm
neeraj-344433 (4/27/2015)
@Sean Lange.I certainly have no intention to ignore anybody who is trying to help me. If thats the message being conveyed, my apologies.
So lets start from beginning (given i have read the article you provided this time.)
We have a process which runs every night which extracts data from SQL server and performs the indexing exercise on a unix box using that data. Below are the steps in which this process works
1. A stored procedure is executed and it truncate and loads the data in staging table (stg_IndexDetails)
2. The staging table has 85 columns and about 3 million records which are loaded every night as part of the above mentioned truncate and load process.
3. Then this table is queried as (Select * from stg_IndexDetails (NOLOCK)) and using the result set of the query, the indexes are generated on the other side.
4. This is not an one by one operation as process captures the whole resultset and then loops through it. This is a third party application so no control over it about how it processes this data.
My problem is the performance of Select * from stg_IndexDetails query (step 3). This alone takes about 2 hours to return all the data.
Does this help?
Thanks again!
3. Then this table is queried as (Select * from stg_IndexDetails (NOLOCK)) and using the result set of the query, the indexes are generated on the other side.
Other than putting this data on an SSD drive and using a 10GB network connection, I don't see any way to improve this step.
4. This is not an one by one operation as process captures the whole resultset and then loops through it. This is a third party application so no control over it about how it processes this data.
The fact that it appears that you are looping through the result set on the other end does make it appear to be Row By Row processing.
April 27, 2015 at 1:17 pm
In step three you mention that the data is pulled from SQL Server then processed on the target system, can you verify that the performance issue is actually SQL Server and not the process being run on the target system?
Just a simple run of the select on SQL Server should show you if that's where the bottleneck is.
May 6, 2015 at 8:00 am
Hi all,
Thanks for your valuable inputs, I was able to fix the performance of the select query using Vertical partitioning.
Thanks again!
May 8, 2015 at 12:06 am
neeraj-344433 (5/6/2015)
Hi all,Thanks for your valuable inputs, I was able to fix the performance of the select query using Vertical partitioning.
Thanks again!
For the rest of us who would like more insight into how you define "fix the performance", can you give some stats and more information ? I usually see the phrase 'vertical partitioning' relate to replication. Did you end up instituting replication ? Was the procedure before being called using a Linked Server connection ( which can be painfully slow) ?
Thanks and glad you got this resolved.
----------------------------------------------------
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply