August 9, 2004 at 4:38 pm
We are trying to use MSDE to create a 'Stage Database'. We have a batch process to read from a customer data file (.TXT) file , insert into a local DB (MSDE 2000 RelA). It inserts into just 1 table.
Appears to be a quite straight forward scenario. We use ADO to connect and insert the data. Tested it for a test file containing 156 records. It takes 5 minutes to insert. Tried to insert into a network SQL Server DB 10 sec. Something way off, performance-wise?
Would love to get the MSDE to perform the task at a reasonable pace if not in a blasting speed (big cost saving as the machine to be placed at customer site). Any suggestions on what could be wrong? (Just cannot believe the slowness).
It is a Windows 2003 Standard Edition Server - 512MB RAM, 1CPU. No bottlenecks with CPU or memory. No other active connections open. Only other app running on the machine is a Windows Service to poll for customer file at a specified time interval and insert rec in the DB. I have stopped the other Windows Service to upload data from SQL DB to the central DB using Web Services.
Tested the whole process with regular SQL Server DB 2000 and works really good. Just trying to replace the client DB to MSDE.
Help greatly appreciated!
August 9, 2004 at 5:06 pm
1. Did you consider using a bulk copy to insert the information?
2. Did you do anything different between the two (local and network?)?
3. Are you working with the data as you transfer it in (modifying it in any way)?
4. When you say "we use ADO"...what type of app are you using to import the file?
Need a little bit more information to try to help.
Regards,
Joey Filichia
August 9, 2004 at 11:33 pm
Do you have any triggers on the destination table ? If yes, they may be the cause of the slowness. Tell us what the triggers are doing (and how), and maybe we can improve them.
Razvan
August 10, 2004 at 9:47 am
1. Did you consider using a bulk copy to insert the information?
> No. because we need to log errors on a record level. We were parsing / formatting, validating and inserting the records into the DB
2. Did you do anything different between the two (local and network?)?
> None that I can think of except that the network SQL server has more memory. All I did was change the DSN to point to network DB instead of local. NO OTHER CHANGES.
3. Are you working with the data as you transfer it in (modifying it in any way)?
> No modifications. Just the standard - parse/format,validate and insert. The only additional logic is to perform an insert or update based on the key. There is an index on the key. I truncated the table before trying to process the 156 odd rec. So, data size also does not come into play.
4. When you say "we use ADO"...what type of app are you using to import the file?
> It is Windows service developed using VB.NET. We use ADO.NET (ADODB) because we need the flexibility to connect to diff database.
Do you have any triggers on the destination table ?
No triggers. Straight forward insert/update into a table. The data in the table mainly containing shipping information.Timestamp, accnum, Pickup name&address, delivery name&address and few other delivery related fields.
I am going to try creating a stored procedure instead of the dynamic sql statement and see if that makes a difference for MSDE. (We usually use stored procedures for all our database access. We did not it here since it was a relatively simple processing).
August 10, 2004 at 11:55 am
I think doing a stored procedure is the best idea because it's compiled code that lives on the server itself. It should increase the performance. Dynamic SQL takes longer simply because it adds steps into the process that aren't needed. To my knowledge, procedures are the recommended "best practice" for Dot.Net.
Keep us updated.
Joey
August 10, 2004 at 12:15 pm
1. Any blocking occured?
2. How many sessions did you see duringthe insert? The Max sessions MSDE can support is only 8 sessions concurrectly.
August 10, 2004 at 3:39 pm
No blocking at all. Infact I tried to close even the enterprise manager when uploading data since I did not want to add an extra connection (primarily for the restrictions that MSDE has). When I tried to trace, the only activity I saw was the insert statement.
I also turned off autoshrink and auto update statistics to avoid extra overhead and also expanded the initial size of the DB and log to avoid frequent expansion.
I would be interested what rating on a scale 1-10 would you give for the performance of MSDE for small size applications ? There are very strong reasons for me to get the MSDE to work since we are preparing this machine / app to reside at customer location. The volume of data to be processes is not in the excess of 2000 per day. There is just 1 site which processes 16000 rec / day. That is exactly why we favor MSDE. Moderate speed will suffice but 6 minutes for 150 records according to me is a absolute crawl.
Inputs / ideas welcome...
August 11, 2004 at 7:19 am
What abot system resources usage like CPUs?
August 11, 2004 at 4:27 pm
There's some other variable in your test. I work with MSDE all the time, and it is not any slower than SQL Server; It IS SQL Server. MSDE does throttle performance after 8 simultaneous actions. But if your import application is the only thing running, and it's not multi-threaded, I don't see how you could be triggering the throttle.
I just did a test and inserted 156 records in 3.5 seconds using ADO and MSDE
Randall Moore
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply