Database Limit

[mwedberg]

Latley we're having some strange behavior in some large databases. Sometimes the queries just takes a long time to run. They've run fine earlier. Is there a limit in rows/data that one should stribe to not exceed? It's on a fast machine with almost 12Gb free space. One table e.g has 1126320 rows. The database in question takes up 1845 Mb after a shrink logs. I'm not sure if trunkating the large tables or remove tables not beeing used would be best. Greatful for any input.

[extofer]

do you have capabilities to run stored procedures??
also, do you use indexing.... I know that in times you have to run large quirries, but if your DB is indexe correctly, that should help the process. Secondly, if you run a stored procedure, (ie. Oracle, SQL Server) the proccess is also faster.

[mwedberg]

Yes they are indexed and we have some stored procedures, it's been running well earlier but suddenly it's like it's (sometimes) laggy. Thought that maybe ms sqlserver gets to a point due to size were performance goes down.

[hollystyles]

Yes indexes are your first concern. !. do you have any, then 2. are you indexing the right columns (typically the ones involved in JOINS)

Next are your statements optimized ? (could write a book on this he he) but take the particular query you mention try to ensure the first criteria in the where clause is the most restrictive (ie reduces the result set the most) for example. Search the NET for query optimisation there's stuff all over.

Also how much RAM does the server have and what else is running on it (if anything) other than the database. Queries return quickest when the databse is able to keep lots of the database tables in RAM, as the database grows beyond the RAM queries can be slow/fast depending on the data you are after has been swapped out of RAM or not. If it has the db has to read it back in from disk Slowwwww !