July 1, 2007 at 10:20 pm
This is really strange. I have a brand new server running 64-bit Windows and installed the x64 SQL Express edition. The database was copied from a 32-bit server where it is in a live environment.
The most complex view is taking 26 seconds from within SQL Mgt. Studio or my application. However, if I make any change in the design within Mgt. Studio (even adding a space), then run the query, it takes 1.6 seconds. It will only take 1.6 seconds from that point forward. Leaving Mgt. Studio open, my application now has a 1.6 second response time on every subsequent query of that view.
As soon as I close SQL Mgt. Studio, response time returns to 26 seconds. This condition is very consistent and repeatable.
Am I missing a setup parameter for query optimization to use the indexes?
I don't think this is query related, but I'll post it below. The EQUIPMENT table has 25 rows. SENSORSUMMARY has 740,000 rows. Indexes are available on every field used in the query.
SELECT * FROM dbo.equipment
INNER JOIN
dbo.SensorSummary ON dbo.equipment.npk = dbo.SensorSummary.nequippk
WHERE
(dbo.equipment.lactive = 1) AND
((CONVERT(VARCHAR, dbo.SensorSummary.tlogdatetime, 25) + dbo.SensorSummary.cEquipID) IN
(SELECT CONVERT(VARCHAR, MAX(tlogdatetime), 25) + cEquipID AS Expr1 FROM dbo.SensorSummary AS SensorSummary_1
GROUP BY cEquipID))
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy