SQL save slow when compared with mySQL

[2969]

hi

I have 2 databases (the size are same). When I sue my VB program to save data to mysql, it is much faster than SQL server. I have checked all the indexes on the relevant tables but still slow. For a data that takes 2 sec to save in mySQL, it takes aound 8 seconds on SQL. Again, the sizes and other characterisics are same for both DB.

 

[SQLSister]

So exactly how are you saving data? Please post the code.

Questions about posting. See faq183-874

 

[mrdenny]

Take a look at the query plan in Query Analyser. That should tell you where your latency is.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[vongrunt]

Are we talking about one/few complex queries or lots of while-not-eof-do-something queries initiated client-side? More info please...

 

[SQLSister]

You didn't by chance use a cursor did you?

Questions about posting. See faq183-874

 

[2969]

how can i find out if I am using cursor on SQL side. As far as VB app is concerned, the code is same ad I just change the DSn to talk to mySQL or SQL.

 

[mrdenny]

You would need to look at the code that is being run on the SQL Server, or at the procedure that is being called.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[2969]

i am using a server side cursor ..here is the snippet.

mo_CnLib.CursorLocation = adUseServer

 

[2969]

Also, the save involves direct insert commands..it seems that mySQL inserts are faster than SQL inserts.

 

[mrdenny]

What are your memory configs setup like? Does SQL have enough memory, are your disks fast enough? Are you using an engine in MySQL that has transaction logs? If not that's one reason that it will be faster.

I'm curious why it's taking 2 seconds on the MySQL side to do the inserts. Inserts into any database should be very fast.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

I'd still like to see the code that actually does the insert. There are many very inefficient ways to write code.

Questions about posting. See faq183-874

 

[mrdenny]

This is true. Can you post the insert code.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[donutman]

Where are the server engines? Are they both on your workstation or on a remote server?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

I meant SQL engines not server engines?