[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How to partition a large table in to a TEMP TABLE to help reduce Stored Proc Processing Times??

Posted on 2014-07-10
Medium Priority
Last Modified: 2014-08-04
I have a stored procedure that uses a sql script that contains a table that has over 20 million rows of data. Processing of this sql script takes longer than it should.

What is the best way to deal with LARGER ROW tables to help reduce SPROC Processing times?

I've tried to pull only a specific date range ( last 2 years, ie. getdate() - 720 ) from this table, but it still takes a long time to pull the data into the temp table.

What the best techniques to use?
Question by:MIKE
LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 40188927
The best way is to first make the sure the table is properly clustered, i.e. that it has the best clustering index key(s) in effect.  This applies to all tables, but especially to large tables.  Typically 20M rows isn't an issue for SQL unless it has to scan the whole thing (perhaps even more than once).
LVL 17

Author Comment

ID: 40188933
Good point,.. I stand corrected,.. this involves a VIEW, which is a view of a LARGE table.

The deal is, this is a government contract I'm working on which is in relation to very large table that is managed/governed by another National IT location. We are allowed access to the Table via VIEWS.

I cannot change the view.

I can however, pull data from the table using the VIEW and place the needed data in another DB where I can do whatever I want. I can assign indexes, etc...AFTER the data is extracted / moved. But again, that would take time.

Any thoughts?

Is there anything.... regarding SQL SYNTAX or SQL SCRIPTING itself that would help speed up my query?

I've tested things like using WITH clause, instead of temp table,...didn't seem to matter...
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40188940
I would have to see the query to tune it.

Expert Comment

ID: 40189079
could it be possible for you to give little bit more detail on what you try to accomplish, like you need data for processing and displaying real time or once in a day you can get data from that remote server (view) and store it in your local db? we really appreciate little more info to assist you
LVL 49

Expert Comment

ID: 40189626
What is the method of access to those views?

The SQL might be very slick already but still run darn slow due to the method of access.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question