Click here to Skip to main content
15,035,723 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi all,
i have written a program that each 10 seconds hit a query in sql server 2012 db and than refresh a graphic page; this page is function of a single record from 4 different table in the database.

The db has set his maximum size as 1024 MB, while his minimum size is 0.
The db save a row each minutes, than in just 1 Month there are over 50.000 record each table, so the growing memory consumption (observed by Windows perfomance meter) raise the maximum size.

When the maximum has achieved, a later query (just a record) selected from these 4 table, takes about 15 seconds and the UI looks like freezed.
I have observed that instead a single record asked, SQL returns the entire table; i think cause it is cached...

Any suggestion to improve the speed?

Thanks

What I have tried:

DBCC... buffer clean... eccetera
Posted
Comments
ZurdoDev 12-Feb-16 15:31pm
   
Indexing.
Member 11727760 12-Feb-16 16:07pm
   
I think is not a query problem, i use the where clause with a single field named 'key' that is a string formatted with the date info and minutes details.
This field is also indexed.

I hope there is an unknown configuration option of SQL server, because this delay happened when the used memory raise the maximum size limit.
Sascha Lefèvre 12-Feb-16 15:33pm
   
"I have observed that instead a single record asked, SQL returns the entire table"

How did you tell SQL Server to return a single record? Maybe show your SQL query here.
Member 11727760 15-Feb-16 8:34am
   
"SELECT * FROM [Temperatures] WHERE IdMachine = " & Id & " AND Key= '" & Key& "'"
Sascha Lefèvre 15-Feb-16 9:10am
   
You don't explicitly tell SQL Server here that it should return only a single record. If your constraints (IdMachine and Key) match more than one record, all matching records will be returned. To explicitly restrict the result to one record you could use the TOP-clause:
SELECT TOP 1 * FROM ...
Member 11727760 16-Feb-16 12:23pm
   
The 'Key' named field is unique; it is a string field with a date formatted into.
With this assumption do you think changing the query from the original "Select *..." in your's "Select TOP 1 *.." should make it quickly?

I have observed that the entire Table is loaded into memory, for future query.. but in this case, when the memory raises the maximum value (cause the record table raises...) then disk access happening.
Sascha Lefèvre 16-Feb-16 12:37pm
   
Well, there has to be some reason that you get more than one record returned - so probably one of your assumptions is incorrect.
Member 11727760 16-Feb-16 14:28pm
   
Sorry, maybe i was misunderstood. After the query it was obviouvsly returned me just a record, the 'where' clause with the field named 'key' select only one row.
Instead a query that asked a single record, i have noticed that SQL server load the entire table in memory.
When the table, that is growing in time, become bigger to raise the maximum size of memory reserverd into SQL server the query become slowly;
Should the "SELECT TOP 1..." than "SELECT *..."'s solution make it better?
Sascha Lefèvre 16-Feb-16 14:40pm
   
Ah, alright, got it now.
I don't think TOP 1 will help in that case but I'd give it a try anyway. And, like GhostHost said, if you don't actually need all the columns, don't use SELECT * but list the columns you need individually.

1 solution

1 - Make sure that your query is optimized, for example avoid SELECT * you should individually include the specific columns that you need.

2 - Analyse query Execution Plan

3 - Profile your database on SQL Tuning mode and try to use Database Engine Tuning Advisor

4 - If is possible increase memory that SQL can use
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900