Create and then update a Table from a Stored procedure in DTS
-
Hi all,
Can someone please help.
I have a stored procedure in SQL 7 that conatains a query that returns certain data from my SQL Database .This data is displayed on a webpage.
The page would load much quicker if I linked to a dynamic table.Rather than running the Stored procedure each Time on page load.
How do I use DTS to Create a Table from this returned data and to update the Table every 5 minutes by running the Stored procedure again ?
What Task in DTS do I need to use? and then Schedule so that the Table is updated..The table can be wiped and then created again each time ...Is this the way to do this?
Please Please help.
Ray..
-
Can anybody even give me an idea of how to do this ?
Ray..
-
1. I assume the stored procedure returns data from multiple tables, because if it returns data from one table there would be no speed increase.
2. You do not need a DTS package to create your table. Just schedule a job to run every 5 minutes to execute a stored procedure that creates your new table.
3. Wipe it and then create it again? It depends on the size and if you have somebody accessing the table at the moment when it is empty or while you are populating it. He/she will get nothing or it will be slow again, because while you are inserting, you will lock the table. The best sollution for the user is when you wipe a Tempory table and inserting the data into the Temporary table. Then you use the right logic to update your permanent table. There is more for the stored procedure to do but the user will get the best experience you can give him/her.
5ilverFox
Consulting DBA / Developer
South Africa -
I agree, you have no need for DTS in this case.
But I disagree with that you cannot get speed increases if your SP only queries one table (imagine aggregation functions on a 100+ GB table).
I would like to point out that this sounds like a totally normal problem that you can solve in a good way implementing a data caching pattern.
But if you only want to solve your problem in a non generic way I think you should go with solution 3 by Japie.
With the slight modification that instead of wiping the table you are storing your cached results in you:
1) Insert your data into a staging table (tmptabA)
2) Apply any needed permissions to your staging table
3) DROP your data table (tabA)
4) Rename your staging table to your match the name of your data table (RENAME tmptabA -> tabA)
This way you do not have to lock the table whilst updating it with new data...
Hanslindgren
-
Will teach me to write full sentences. 🙂
I meant that there should not be a major speed difference between querying a table direct or using a stored procedure to query the table.
5ilverFox
Consulting DBA / Developer
South Africa -
And that I fully agree with
-
Thx for the reply guys..
So you think that using DTS is overKill ?
I did try to create a table using "create into" but It will not let me over write the existing table when I run the stored procedure again.
I assume that this is why you suggest the Temp Table.
So My stored procedure should do the following?
1 . Insert My data into a staging table (tmptabA)
2 . DROP My data table (tabA)
3. Rename your staging table to your match the name of your data table (RENAME tmptabA -> tabA)
My question is....Is this the standard way to do this type of thing...
and will this take just as long to do so that there is no performance increase.
as you said the table might get accessed while in the process of being renamed..
I was considering DTS so that I could schedule the stored procedure but Can this be done in SQL without DTS
Thx
Ray
-
Yes. DTS is overkill. You only want to execute a few simple T-SQL statements, you have no need of anything you cannot conveniently find in T-SQL.
This can be sceduled as a job, the same way a DTS can be scheduled within the SQL Agent (of course both DTSes and T-SQL batches can be scheduled and launched from the commandprompt if it would have been neccessary).
I would say it is rather standard since it boils down to a small set of actions that are very generic and does not really depend on the data.
Usually you would implement your caching in your DAL (data access layer) and maybe even in your Serverside in your website. It depends on your number of tiers and how well separated your business logic is from your persistent data store...
Regards,
Hans
-
Hi Again Guys..
I have been using the procedure you gave me with no trouble so far.. But now I have an issue with NULLS
As discussed I create a temp table from a query result but this table contains empty values in some columns that need to be NULL instead.
My Web Application that reads this Table Needs Empty Data to Be Null
By Default, the table Design created by using CREATE INTO doesn't allow Nulls and so Conatins empty string data.
Is there a way to change this.
Is there a way in SQL to Insert Nulls in the Returned Query Columns that contain no Data ("") .When you open the Table in SQL some Column Data is just empty instead of <NULL>
Thx again
Ray..
-
Hello,
To replace empty values with NULL use the function NULLIF( myColumn , '' ).
Example:
CREATE TABLE #myTable ( myColumn VARCHAR(50) NULL )
INSERT INTO #myTable (myColumn) VALUES ('Test')
INSERT INTO #myTable (myColumn) VALUES ('')
SELECT NULLIF( myColumn ,''), myColumn FROM #myTable
SELECT NULLIF( myColumn ,'Test'), myColumn FROM #myTable
Then I would suggest you NOT to use CREATE INTO because that gives side effects you do not want in the long run.
Regards,
Hanslindgren
-
If the bottom line is getting the result set back to the client quicker, maybe you need to build some indexes to assist this procedure. Or maybe the indexes you think are being used are not. Maybe an index hint is required.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply