Should I use stored procedures?

Question

I am currently working on a project to export data from an application's database to a flat file for use in another application. This process needs to run every 5 minutes or so. However, this database usually has about 40+ users accessing the table i need info from. I am currently using an ado 2.0 command object with VB 6 to execute a series of transactions to pull the data into a seperate table so i can work with it without harming performance. However, I get numerous timeouts and deadlocks when trying to extract the data from the core table. Would the use of a stored procedure to carry out these transactions reduce the amount of issues occurring? Any help or thoughts would be greatly appreciated.

Answer 1

Can you post the query that you are executing every 5 minutes?



Generally speaking, stored procedures will execute faster. But if you are executing a sub-optimal query (i.e. you are forcing a table scan or some other resource-inefficient method) you probably will not benefit by using a stored procedure.



How large is (i.e. how many rows are in) the "core table?"



How do you accomplish this step:

pull the data into a seperate table so i can work with it without harming performance

Are you using a single "select into" statement or are you executing a select and then executing an insert for each selected record?







*** ANSWER UPDATED ***



Do you need to abstract your table into a view? It doesn't seem necessary, and will make indexing difficult.



In any event, this is your bottleneck:

"casting a date field to a smalldatetime as UPDATED."



1) Do you have to cast it to smalldatetime? Querying in this manner will force a full table scan. This means that SQL Server will go through every single one of the half-million records, cast it to a small date time, and compare it to your queried value. If any indexes exist, they will be ignored. (Think of it as you having to scan through a phone book looking for a number, except that the phone book is not alphabetized).

2) If you remove the cast and create an index, SQL Server will spend much less time and energy locating your records.



Assuming "KAGL_TMW_ORDERS_TABLE_NOT_VIEW" refers to the table (not the view), and "THE_ACTUAL_FIELD" refers to the field in the table (not the casted field in the view), try this:



CREATE INDEX KAGL_TMW_ORDER_DATE_IDX ON KAGL_TMW_ORDERS_TABLE_NOT_VIEW (THE_ACTUAL_FIELD)



INSERT INTO KAGL_TMW_NEWORDER SELECT * FROM KAGL_TMW_ORDERS_TABLE_NOT_VIEW WHERE THE_ACTUAL_FIELD >= @MINDATE AND THE_ACTUAL_FIELD <= @MAXDATE







*** ANSWER UPDATED ***



Too bad you don't have a mentor to work side-by-side with. It's a big help... But Yahoo! Answers is the next best thing!



Here's an article that describes how to create a DTS package and then put it on a recurring schedule using the SQL Server Agent. Should be of help to you.



http://articles.techrepublic.com.com/5100-6329_11-5031813.html

Answer 2

A stored procedure will generate the query plan once and execute it later with that execution plan when it is called, this usually means it will run faster, 'cause you rule out the compile option each time you run the sp instead of executing the commands on a batch. since data changes and so do index optimizations in your case, you will have to consider checking every 2 weeks or so how the sp is performing, since one execution plan maybe be right one week, but with more data, it won't next week.



-Check the select's execution plan to see if it's using an index, you do have to be careful with table scans cause you will have more and more timeouts and deadlocks (Don't really know how you can get a deadlock since a select does a shared nonexclusive lock).



-If you are performing a table scan, consider adding an index for the field(s) you are limiting data for. Be careful cause although this will make your queries faster, it can also slow down your inserts and updates.



My personal opinion is to improve your query's speed by checking how it is delimiting the data, this has to be as fast as you can. then use a Dts instead of a program, that's what they're for. I'm assuming you're using MS Sql Server.



Hope this helps. Cheers

Answer 3

Maybe use stored procedure because speed increase?

Answer 4

are you using (nolocks) statement on your tables?