Jump to content

  • Log In with Google      Sign In   
  • Create Account

C# SQL connector very slow


Old topic!
Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.

  • You cannot reply to this topic
18 replies to this topic

#1 yewbie   Members   -  Reputation: 665

Like
0Likes
Like

Posted 22 May 2012 - 11:41 AM

First off let me preface this by saying that I am very new to using C# to do anything with SQL so I may not know some of the basics =)

I am currently using MySql.Data.MySqlClient and have written a custom C# application to parse records from a CSV file and update some records in my table and insert some records as needed pretty simple stuff.

Now the underlying code to do this works great. My problem is that is it is very very slow, everytime I do a SELECT or UPDATE, it can take upwards to a second to complete.

For each record I am doing a select to find the reference ID for the line in the table.
I then use that reference number to update a couple of fields based on the data I parsed.

I am dealing with a table that has over 200000 records and yes I need to go through every single one because the data I am updating can go all the way back to the beginning so I can't LIMIT my select.

Since I am new to SQL in general is it better to batch updates? IE say I have 700 updates just mash as many as I can into a single SQL statement and push that?

I don't really understand the connection pooling since right now I am doing a select to find my reference number before I update, but really I am just looking for any advice =)

Thanks in advance!

Sponsor:

#2 ApochPiQ   Moderators   -  Reputation: 16414

Like
2Likes
Like

Posted 22 May 2012 - 11:44 AM

Sounds like the problem is your query itself, not C# or something inherent to SQL. Is your table appropriately indexed?

#3 yewbie   Members   -  Reputation: 665

Like
0Likes
Like

Posted 22 May 2012 - 12:37 PM

Sounds like the problem is your query itself, not C# or something inherent to SQL. Is your table appropriately indexed?


My table is indexed by a auto incrementing primary key (is this bad?)
My select is basically like this when I find the reference ID to to the actual update.
SELECT `PrimaryKeyID` from `MyTable` WHERE `SerialNumber` = 'blah'
also
SELECT `PrimaryKeyID` from `MyTable` WHERE `SerialNumber` LIKE 'blah'
I've tried both they return at about the same speed.

my update is
"UPDATE `mydb`.`MyTable` SET `FinalSerial` = 'blah' WHERE `MyTable`.`id_sn`= PrimaryKeyID;

I typed these out because much of it is obfusticated by variables being insert so there may be a typo but this is the gist of it.

#4 ApochPiQ   Moderators   -  Reputation: 16414

Like
2Likes
Like

Posted 22 May 2012 - 12:56 PM

Well, if you do a lot of searching by SerialNumber (as you indeed appear to do) you probably want an index on that column.

#5 Telastyn   Crossbones+   -  Reputation: 3730

Like
1Likes
Like

Posted 22 May 2012 - 01:06 PM

Since I am new to SQL in general is it better to batch updates?


Absolutely.

IE say I have 700 updates just mash as many as I can into a single SQL statement and push that?


That's marginally better, but an actual bulk copy tool/commands (depending on your DB implementation) would be better. Another thing to be aware of is if your environment is using auto-transactions. If it's turned on, then your entire input will be done within a transaction which causes a lot of performance issues for tons of changes like you're looking for.

#6 yewbie   Members   -  Reputation: 665

Like
0Likes
Like

Posted 22 May 2012 - 01:42 PM

Well, if you do a lot of searching by SerialNumber (as you indeed appear to do) you probably want an index on that column.


Currently looking up infromation on index's (I've never used one).
But on a cursory glance it looks like I just add a index and all of my querys on that column as just faster? Or do I have to change my query to reflect using a index?

something like?
CREATE INDEX MyIndex ON table_name (column_name);

It just seems too magical to just work =), on my 3rd reference on index and still no mention of changing the query (maybe they are just bad references?)

Edited by yewbie, 22 May 2012 - 01:43 PM.


#7 Telastyn   Crossbones+   -  Reputation: 3730

Like
1Likes
Like

Posted 22 May 2012 - 01:48 PM

But on a cursory glance it looks like I just add a index and all of my querys on that column as just faster?


'all' is a bit strong, but usually yes. The price comes that inserts, deletes and some updates will (generally) be slower and the table will take up more space on disk.

#8 yewbie   Members   -  Reputation: 665

Like
0Likes
Like

Posted 22 May 2012 - 01:55 PM


But on a cursory glance it looks like I just add a index and all of my querys on that column as just faster?


'all' is a bit strong, but usually yes. The price comes that inserts, deletes and some updates will (generally) be slower and the table will take up more space on disk.


I will give it a try, thank you for your advice =)

#9 yewbie   Members   -  Reputation: 665

Like
0Likes
Like

Posted 22 May 2012 - 03:17 PM


But on a cursory glance it looks like I just add a index and all of my querys on that column as just faster?


'all' is a bit strong, but usually yes. The price comes that inserts, deletes and some updates will (generally) be slower and the table will take up more space on disk.


Just to give you an update, you were exactly correct adding an index to my serialnumber field brought my per transaction upload time from ~1.5 seconds per unit to ~.2 seconds per unit (amazing!)

Now to do some batching and I think it will be "fast enough" for me =)

#10 Postie   Members   -  Reputation: 1121

Like
1Likes
Like

Posted 22 May 2012 - 08:55 PM

Batching the updates together will make a definite improvement. Try something like 100 or 1000 and see how you go. Another thing to look at is how you're building the query string you're executing. If you're concatenating a lot of strings together it will severely impact your performance, as .net has to reallocate the string every time you modify it. The recommended way to build strings is with the StringBuilder class.
Currently working on an open world survival RPG - For info check out my Development blog: ByteWrangler

#11 Washu   Senior Moderators   -  Reputation: 5605

Like
1Likes
Like

Posted 22 May 2012 - 09:37 PM

For a bulk query, turn off auto transactions and wrap the entire query in a single transaction (how you turn them off depends on your SQL engine).

Indexes don't magically make things faster, they improve performance on queries that involve lookups based on the indexed column, at the cost of additional time spent during inserts, deletes, and some updates (ones that involve the indexed column[s]). A select query does not depend on indexes, that is up to the query analyzer (in your prefered SQL server engine) to determine if there are any relevant indexes and to use them. I would highly recommend you get a book on the basics of SQL.

A basic data-structures course would also get you on track with sufficient knowledge to at least understand (to some degree) the underlying data-structures behind tables and indexes.

If you're familiar with data-structures, then I would highly recommend a book like T-SQL Querying. While it is specific to Microsoft SQL-Server, the coverage of the underlying mechanisms for clustered tables, non-clustered tables, and indexing is sufficient to give you a good idea of how most SQL engines work.

Edited by Washu, 22 May 2012 - 09:42 PM.

In time the project grows, the ignorance of its devs it shows, with many a convoluted function, it plunges into deep compunction, the price of failure is high, Washu's mirth is nigh.
ScapeCode - Blog | SlimDX


#12 yewbie   Members   -  Reputation: 665

Like
0Likes
Like

Posted 23 May 2012 - 08:44 AM

If you're familiar with data-structures, then I would highly recommend a book like T-SQL Querying. While it is specific to Microsoft SQL-Server, the coverage of the underlying mechanisms for clustered tables, non-clustered tables, and indexing is sufficient to give you a good idea of how most SQL engines work.


Honestly I have large gaps in my knowledge since I don't do this professionally, just as a hobby. (The first step is admitting you have a problem right? hah)
I am using mysql right now.
For $30 and the prospect of continuing to work with SQL I will give it a read, thank you =), I am also going to order a more basic SQL book so I can work my knowledge up from the ground up with a better foundation than I currently have.

#13 JohnnyCode   Members   -  Reputation: 329

Like
0Likes
Like

Posted 24 May 2012 - 04:58 PM

For each record I am doing a select to find the reference ID for the line in the table.


nooooooooooooooo! bnoooooooooooooooooooo!
man. you get imposible amount of results and you perform update from those results? it takes second? what machine have you got?
Use JOIN to get all neede rows and then

why not?
UPDATE row IN tableofmine WHERE conditionismet SET "myvaluesextraction"


#14 JohnnyCode   Members   -  Reputation: 329

Like
0Likes
Like

Posted 24 May 2012 - 05:02 PM

also, if you want to update ten records you need not to call ten updates just call one update with propriate condition, but the values must match. if they do not, you need to perform multiple updates

#15 JohnnyCode   Members   -  Reputation: 329

Like
0Likes
Like

Posted 24 May 2012 - 05:03 PM

but this happens only at databases migrations, not regular web applications continuing

#16 JohnnyCode   Members   -  Reputation: 329

Like
0Likes
Like

Posted 24 May 2012 - 05:21 PM

SQL server is a poor boy who can not be balanced ! when you query SQL boy... please. ..... form your question so that he provides you with all data you can possibly need and you then request them on order you have specified. This is a powerfull feature . So, get result just, do not get it so fast.

#17 ApochPiQ   Moderators   -  Reputation: 16414

Like
1Likes
Like

Posted 24 May 2012 - 06:00 PM

Please don't post four times in a row in the same thread. If you have something to add to a post, use the Edit button.

#18 JohnnyCode   Members   -  Reputation: 329

Like
0Likes
Like

Posted 24 May 2012 - 06:19 PM

Please don't post four times in a row in the same thread. If you have something to add to a post, use the Edit button.


Right. I understand. But doens it keep nice schedule of post?......but I see your point

#19 Washu   Senior Moderators   -  Reputation: 5605

Like
0Likes
Like

Posted 24 May 2012 - 06:32 PM


Please don't post four times in a row in the same thread. If you have something to add to a post, use the Edit button.


Right. I understand. But doens it keep nice schedule of post?......but I see your point

Your first three posts have a total of 5 minutes between them. That's edit time. Your last post is 18 minutes later, and (had you edited the first post) would have been fine.

As an additional note, your last post appears to be generally unreadable.

In time the project grows, the ignorance of its devs it shows, with many a convoluted function, it plunges into deep compunction, the price of failure is high, Washu's mirth is nigh.
ScapeCode - Blog | SlimDX





Old topic!
Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.



PARTNERS