June 3, 2009 at 5:33 am
DB recovery method is set to FULL recovery. Weekly full backups and daily TLog backs are being carried out. DB and log files on different physical drives.
What is the most efficient and bewst practice way of interacting with the MS SQL 2005 DB?
Current 2003 VB.Net app
calling a stored procedure per insert, per query using .NetSQLAgent?
sending text file with paramaters for insert or query via TCP ports or http/s to a webservice that kicks off a batch insert statement or stored proc on the server
There are numerous options in doing this but what is the MOST efficient and most commonly used approach that yields best performance?
We are planning on re-writing a application and would like to know what is done, what works, what doesn't work ... upgrading the code to 2005 or 2008 VB.Net is on the cards ...
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
June 3, 2009 at 8:35 am
How many rows are you inserting/updating at a time? How often are you doing the updates/inserts? Is SSIS an option? Does it have to be real time or can it be scheduled?
I prefer to use stored procedures for all data access and manipulation by applications. I consider SP's to be the API for accessing the data. By doing this I only need to grant rights to the SP's and not the base tables. If you are talking about a bulk loading type of situation then you should evaluate SSIS, bcp, and Bulk Insert.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 4, 2009 at 1:25 am
Thanx for your reply Jack...
Currently we have a .net 2003 desktop application that downloads a tag_id with a date and time stamp per line from a reader. Each ready has 1 batch when it is downloaded though the app and then gets gets batchid from header table and the individual lines get inserted into a details table. Currently if there is 50 scans then there a 50 stored proc calls to insert into the details column and 1 stored proc call to insert into header table to get the batchid.
Centralized server and so far 3 countries are accessing it for downloads and +- 250 reps in total with average 100 scans per reader that download from 35 different locations in the 3 countries.
Most downloads are done during 7 - 9AM and 4 - 5PM
We are planning a rewrite of this application and are looking @ different options to do this. The downloading is the most recourse intensive process and want to streamline it and possibly to the same for the rest of the applications processes which is selects and updates, (no deletes)
Current DB size is only 6Gigs but it is growing exponentially.
To do the downlaoding with 35 downloads @ a given time with 100 lines average being inserted will bulk insert, bcp or individual stored procs be BEST PRACTICE???
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
June 4, 2009 at 4:11 am
Some extra info,
I went through a trace file and a batch of 922 scans takes +- 4 seconds to complete in our Live production evironment to insert via 922 details stored procs insert commands and 1 header stored proc insert.
Comments are wlecome if this can be improved ...
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
June 4, 2009 at 5:37 am
As Jack already wrote. If you work with batches you should have a look for SSIS or bcp. Import the complete batch into staging tables. After this you are able to work on the complete batch instead of row-by-row handling.
This should improve your performance.
How to Post Data/Code to get the best Help How to Post Performance Problems
June 4, 2009 at 6:50 am
THANX!!!
Will probably send a text file to the SQL server per download or batch of downloads and then create a SSIS package to do the rest ... ;-);-)
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
June 4, 2009 at 9:10 am
If you can upgrade to SQL2008, you may want to review this recent article:
http://qa.sqlservercentral.com/articles/SQL+Server+2008/66554/
June 17, 2009 at 2:43 am
What we discovered with this problem or apparent problem is that we call a stored proc to insert the batch into the DB. pass parameters to the stored proc and then the sqlcommand users the .update command on VB.net to update with a Dataset. When this executes in the SQL profiler the strored proc is executed for each row in the dataset on the server so 1 call from the VB.Net app and numerous stored proc executes on server side per line of the dataset.
What we thinking on doing is creating another stored proc to accept a XML parameter and then from the VB.net app pass a XML file with the batch details in it.
Which will execute quicker???
Or are we on the wrong path???
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
June 17, 2009 at 2:58 am
As Ken mentioned, if upgrade to SSE2k8 is an option you should do this and use the new table data-types from .Net side.
If you are not able to use SSE2k8 at the moment I would advice to use XML. It enables you to work set-based.
If your procedure is a usual INSERT or UPDATE procedure a common error might be working without .Net Transactions. If you don't use an own transaction .Net opens a new transaction for each execution of your SqlCommand.
If your procedure is one procedure for INSERT or UPDATE this usually causes a bad execution plan. Use one for each action.
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
June 18, 2009 at 1:29 pm
Use a code like this to pass multiple rows to a SP the insert them all in a single transaction:
Create procedure [dbo].[SGA_SP_EME_GET] (
@strXML xml
)
as
begin
declare @xmltablet TABLE(
pestana int null,
espesor int null,
producto int null
)
insert into @xmltablet
select
Tbl.C.value('@pestana', 'INT') ,
Tbl.C.value('@espesor', 'INT') ,
Tbl.C.value('@producto', 'INT')
from @strXML.nodes('/root/data/r') AS Tbl(C)
end
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply