limit of queries

[lmcamoes]

Hi

I would like to know if there is a limit number of queries that can be made to a server os MYSQL in one connection.
I have a .net sw that I am developing and it produces 50 queries at the time for each object that I create.
The problem is that I will need 50 objects what will invilve 2500 queire in one go.
Will I have problems with it?

Regards

[mendhak]

You may end up having locking scenarios if they're all trying to update the same database objects. You should consider trying to minimize the number of queries being sent out, group them together. Set a timeout on your queries to avoid deadlocks.

[lmcamoes]

They are only "select", no updates.
I cannot timed them because having data in "real time" is critical.

[mendhak]

The timeout was meant for update queries.

Use WITH(NOLOCK) when selecting.

Optimize your queries. Be efficient...

But I think you'll really need to do some stress testing to see effects. You ideally should not have problems with this as long as you're following general good practices. Return your connection to the connection pool afterwards...

[lmcamoes]

Sorry for my ignorance... but I'm new at this :-(

What do you mean by "Use WITH(NOLOCK)" and by
"Return your connection to the connection pool afterwards..."

I made one update every 5 seconds and 50 selects each 5 seconds...

[mendhak]

NOLOCKS - http://articles.techrepublic.com.com...2-6185492.html

About 'returning', it just means that you should make sure you dispose of your objects properly once you're done with them.

[lmcamoes]

Thank you!

so you think that if I use "good practices" I should have no problems with one update and 3000 selectss each 5 seconds.

[mendhak]

Oh, so there is one update. You did say there are no updates in post #3.

But yes, you get the general idea.

[lmcamoes]

yes, I sayd but it was wrong.

So, should be no problem? What is the limit of number of selects?

[si_the_geek]

The limit is not the number of queries - it is the amount of work (which includes the complexity of queries, indexes that can be used, ...), and the hardware available (eg: speed and availability of processor/RAM/...).

Working it out is not easy or accurate, so your best bet (as suggested earlier) is to do some stress testing - try running what you want, and see how well it copes (and use Task Manager to see how much free CPU/RAM there is, as the workload is likely to increase over time).

[FunkyDexter]

Coming at this from a different direction, do you really need to create that many queries? Generally, it's a good idea to use a few queries that return alot of information rather than alot of queries that return a little information. Is there anyway you can issue one query that gets the information for all the objects up front?