Does SQL-server cache inner SQL ?

[jyskebank]

I'm creating a solution, that several times need to make requests to my SQL-server. These requests are in several occasions based on the same returned recordset, so I need to know, if SQL-server 7 caches an inner SQL seperately from the outer SQL?

Ex.
select * from A where ID in (select ID from B where Index < 100)

And afterwards...
select * from C where ID in (select ID from B where Index < 100)

In the example above, does the inner SQL: select ID from B where Index < 100 cache seperately, so that the second SQL in the example will be performed faster than the first?

Regards
Jacob B. Nielsen
Jyske Bank
Denmark

 

[tlbroadbent]

Whether the data will be cached when the 2nd query runs depends on several factors.
[ol][li]Amount of memory available for data cache. The more memory available, the data pages SQL Server can retain in cache.
[li]The amount of memory required to hold the data pages in the question. If the number of pages required to hold the data is small it is more likely to remain in cache.
[li]The amount of activity on SQL Server. A very busy Server may flush cache more frequently depending on the data activity.
[li]Then elapsed time between queries. The shorter the time, the greater probability the data will still be in cache.[/ol]If you have a frequently used, small lookup table, you may want to consider using DBCC PINTABLE.

DBCC PINTABLE marks a table to be pinned, which means SQL Server does not flush the pages for the table from memory. Read about it at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_30it.asp

Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[jyskebank]

Thank you for your quick answer.

My question however was ment in a more general manner, unless I have misunderstood your answer.
I am aware of the conditions mentioned in your answer, and there should be plenty of memory for the caching.

My question was focused on the SQL-servers ability to split up the SQL-statement in order to cache only the result of the inner statement in a seperate memoryspace.
My problem is, that if the SQL-server is not capable of doing this, I will have to 'manually' select the Primary keys from the inner statement, and then write them into the condition of the outer statement, so that the inner statement is performed only once.
- I might be able to use the PINTABLE feature, but I'd rather still be able to cache the inner statement seperately, since I only use each inner statement for a short while (but still many times during this time however).

Jacob

 

[tlbroadbent]

There are two kinds of cache. Procedure cache and data cache. Of these, the most important is data cache. When a data page is read, regardless of whether it is part of a sub-query or outer query or any other query contruct, it is loaded into data cache.

SQL Server also compiles execution plans and loads into procedure cache. As far as I know, that plan would be for the entire query script, not a portion of it such as the subquery.

I'm really unclear what you mean by &quot;I will have to 'manually' select the Primary keys from the inner statement, and then write them into the condition of the outer statement, so that the inner statement is performed only once.&quot; What is the intent? Why perform the Inner statement only once? What happens if the data changes between executions? Are you concerned about performance or is there another issue that I've missed?
Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[jyskebank]

Regarding the last part about the 'manual' selection...
I am concerned about the performance, which is why I want to make the subquery compile and run only once.
- By &quot;'manually' select&quot; I mean, that i'm running the subquery as a seperate query, using this result to generate the SQL (primary keys) for all the outer queries.

Yes, I am aware of the possibility of data-changes, and it is very important, that I do only work on the same set of data during the entire set of SQL-statements. (All the statements are repeated continuosly by a scheduler. So new data will be taken care of in the next run by the scheduler.)

The intension for the entire procedure is to make a synchronization between some tables in two seperate datases.
- The databases cannot be kept in synch by a transaction-server, so I need to make a script that read and synchronize the data in each DB.

Jacob

 

[tlbroadbent]

I think the SQL concepts of compiling and caching are very different from what you are thinking. In order to accomplish what you want, you should create a table, insert the data to be selected by the subquery into the table and then select from that table for subsequent queries. In this way you can be assured that the data is the same for each subsequent query. The table could then be truncated after the entire process completes. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin