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

Posted on 2014-07-10
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 68

    Accepted Solution

    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

    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 68

    Expert Comment

    I would have to see the query to tune it.
    LVL 7

    Expert Comment

    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 47

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now