Trouble with MSSQL query
17 Message(s) by 4 Author(s) originally posted in cfml database access
| From: Henweigh99 |
Date: Sunday, October 21, 2007
|
Hello All,
I have an unusual situation where I am trying to
query the
database (MSSQL)
using an IN statement and the PK
list has approximately 10,000
record s in it.
SQL throws an
error "The query
process or ran out of
stack space during query
optimization" which relates to a limitation in MSSQL Server:
(
http://support.microsoft.com/kb/288095)
Microsoft 's suggestion is to create a temporary
table and then JOIN the two
tables to get the results you are looking for. However, in my particular
situation, I do not know how to create a temporary table from just list data.
Here is an example of the Cold Fusion template:
Step 1: Get a list of IDs that this user is allowed to see
Query a bunch of records and sub records and
generate a list
variable
(#RecordsToGet#)
Note, this query
loop s several times because of
parent /
child relationships in
the table. After each loop, the list is appended to.
Example:
<CFSET currentparent = 0>
<CFLOOP condition="ContinueLoop IS 'yes'">
<CFSET LoopCount = LoopCount + 1>
<CFQUERY datasource="#datasource#" name="getMyStuff">
Select ID, Parent
FROM myTable
WHERE Parent = #currentparent#
AND Criteria = '#bla#'
</CFQUERY>
<!-- assign the new parent to a value list and append the master list -->
<CFSET CurrentParent = ValueList(getMyStuff.ID)>
<CFSET RecordsToGet = ListAppend(RecordsToGet,CurrentParent)>
<CFSET loopcount = loopcount + 1>
(continue loop until there are no more records (i.e. you've
reached the
bottom of the
tree )
</CFLOOP>
Step 2: Get the record details for the matching records in the
(#RecordsToGet#) list.
<CFQUERY datasource=#datasource# name="getTheRecords">
SELECT ID, Name, Description, Image, Etc...
FROM MyTable
WHERE ID IN (#RecordsToGet#)
<-- the problem lies here
AND Name = '#MoreFilterCriteria1#'
AND IMAGE = '#MoreFilterCriteria2#'
</CFQUERY>
I have been able to fix the problem (temporarally) by checking if
#RecordsToGet# is has more than 1000 list elements, and if so, get ALL of the
records in the table and then use a QofQ to
filter out the records.
So the 2nd query above'd become:
<CFQUERY datasource=#datasource# name="getTheRecords">
SELECT ID, Name, Description, Image, Etc...
FROM MyTable
WHERE ID
<CFIF ListLen(RecordsToGet) GT 1000>IN (#RecordsToGet#)
<CFELSE>1=1
</CFIF>
AND Name = '#MoreFilterCriteria1#'
AND IMAGE = '#MoreFilterCriteria2#'
</CFQUERY>
<CFIF ListLen(RecordsToGet) GT 1000>
<CFQUERY dbtype="query" name="getTheRecords">
SELECT * FROM getTheRecords
WHERE ID IN (#RecordsToGet#)
</CFQUERY>
</CFIF>
Although this has temporarally fixed the problem, the performance of these
queries is VERY slow.
Does anyone have any suggestions or tips?
Thanks so much.
| From: GArlington |
Date: Monday, October 22, 2007
|
wrote in message:
Hello All,
I have an unusual situation where I am trying to query the database (MSSQL)
using an IN statement and the PK list has approximately 10,000 records in it.
SQL throws an error "The query processor ran out of stack space during query
optimization" which relates to a limitation in MSSQL Server:
(http://support.microsoft.com/kb/288095)
Microsoft's suggestion is to create a temporary table and then JOIN the two
tables to get the results you are looking for. However, in my particular
situation, I do not know how to create a temporary table from just list data.
Here is an example of the Cold Fusion template:
Step 1: Get a list of IDs that this user is allowed to see
Query a bunch of records and sub records and generate a list variable
(#RecordsToGet#)
Note, this query loops several times because of parent/child relationships in
the table. After each loop, the list is appended to.
Example:
<CFSET currentparent = 0>
<CFLOOP condition="ContinueLoop IS 'yes'">
<CFSET LoopCount = LoopCount + 1>
<CFQUERY datasource="#datasource#" name="getMyStuff">
Select ID, Parent
FROM myTable
WHERE Parent = #currentparent#
AND Criteria = '#bla#'
</CFQUERY>
<!-- assign the new parent to a value list and append the master list -->
<CFSET CurrentParent = ValueList(getMyStuff.ID)>
<CFSET RecordsToGet = ListAppend(RecordsToGet,CurrentParent)>
<CFSET loopcount = loopcount + 1>
(continue loop until there are no more records (i.e. you've
reached the bottom of the tree)
</CFLOOP>
Step 2: Get the record details for the matching records in the
(#RecordsToGet#) list.
<CFQUERY datasource=#datasource# name="getTheRecords">
SELECT ID, Name, Description, Image, Etc...
FROM MyTable
WHERE ID IN (#RecordsToGet#) <-- the problem lies here
AND Name = '#MoreFilterCriteria1#'
AND IMAGE = '#MoreFilterCriteria2#'
</CFQUERY>
I have been able to fix the problem (temporarally) by checking if
#RecordsToGet# is has more than 1000 list elements, and if so, get ALL of the
records in the table and then use a QofQ to filter out the records.
So the 2nd query above'd become:
<CFQUERY datasource=#datasource# name="getTheRecords">
SELECT ID, Name, Description, Image, Etc...
FROM MyTable
WHERE ID <CFIF ListLen(RecordsToGet) GT 1000>IN (#RecordsToGet#)
<CFELSE>1=1</CFIF>
AND Name = '#MoreFilterCriteria1#'
AND IMAGE = '#MoreFilterCriteria2#'
</CFQUERY>
<CFIF ListLen(RecordsToGet) GT 1000>
<CFQUERY dbtype="query" name="getTheRecords">
SELECT * FROM getTheRecords
WHERE ID IN (#RecordsToGet#)
</CFQUERY>
</CFIF>
Although this has temporarally fixed the problem, the performance of these
queries is VERY slow.
Does anyone have any suggestions or tips?
Thanks so much.
Change your SQL to do all the work, see:
http://www.databasejournal.com/features/oracle/article.php/3552521
and generally google for "sql select tree"
| From: cf_dev2 |
Date: Monday, October 22, 2007
|
| From: Henweigh99 |
Date: Monday, October 22, 2007
|
MSSQL Server 2000 Developers Edition with all
service packs installed.
| From: cf_dev2 |
Date: Monday, October 22, 2007
|
I was going to suggest CTE's but that's SQL 2005 specific.
I'd try and move the looping out of
ColdFusion and
int o a stored
procedure . You
could set up a while loop to insert the tree values into a temp table/table
variable, using JOINs instead of an IN (...). Then
join the temp
table/variable back to your main table.
Another
option is to alter the table model to use nested sets. It has its pros
and cons, but one advantage is it avoids the
recursion needed for the
parent/child structure.
http://www.intelligententerprise.com/001020/celko.jhtml
| From: cf_dev2 |
Date: Monday, October 22, 2007
|
Here is a quick example I adapted. I whipped it out fast, so its
[b]extremely[/b] likely there are
syntax errors. Hopefully it'll demonstrate
the concept.
DECLARE @xxxxxxxxxxx int
SET @xxxxxxxxxxx = 0
INSERT INTO @xxxxxxxxxxx (ID, FolderName, Depth )
SELECT FolderID, FolderName, @xxxxxxxxxxx
FROM Folder
WHERE Parent = @xxxxxxxxxxx
WHILE EXISTS ( SELECT 1
FROM @xxxxxxxxxxx
WHERE Depth = @xxxxxxxxxxx
)
BEGIN
--- get subfolders. left join is to prevent
infinite
--- loop due to circular references
INSERT INTO @xxxxxxxxxxx (ID, FolderName, Depth)
SELECT f.FolderID, f.FolderName, @xxxxxxxxxxx + 1
FROM Folder f INNER JOIN @xxxxxxxxxxx b ON f.Parent = b.ID
@xxxxxxxxxxx e ON f.FolderId = e.ID
WHERE b.Depth = @xxxxxxxxxxx AND
AND e.ID IS NULL
SET @xxxxxxxxxxx = @xxxxxxxxxxx + 1
END
| From: ksmith |
Date: Tuesday, October 23, 2007
|
When you use an 'IN" clause, MSSQLServer itself will
run out of
heap if there are too many items used in the list. I have seen this from other CFUsers. Temp table and join is the way to go.
| From: Henweigh99 |
Date: Tuesday, October 23, 2007
|
cf_dev2 ,
Wow, your example has opened up my eyes a
bit . I was not aware that you could
perform WHILE EXISTS (loop) operations within a query. However, I do not see
how that query adds the values of a list into a temporary table.
If I understand your approach, you are essentially creating a temporary table
which contains the
complete record
hierarchy . Then you can use that query
object in the main query as a JOIN instead of the IN statement.
Another way to accomplish this'd be to create a new query object and loop
throught the list and add each
row in a cfloop.
In your experience, what is the performance difference between using DECLARE
to create a temporary table vs. running two large queries and then joining them
in a Cold Fusion query of queries?
| From: cf_dev2 |
Date: Tuesday, October 23, 2007
|
Another way to accomplish this'd be to create a new query object and loop
throught the list
> and add each row in a cfloop.
You want to avoid stepping through the records one by one. While I have not
run any performance tests, I strongly suspect you'd get better performance
using the temp table/table variable. Your db is optimized to work with "sets"
of information. When you insert the values into a temp table you're using
JOIN's (ie an operation that works on a set or table of information). The db
can
handle that more efficiently than stepping through the records one by one.
It also results in a
reduction of the number of loops required. Say your tree
has 1000 records on 9 levels. The db
method will loop only 9 times. Whereas
you'd be looping 1000 times if you were to create a separate query.
| From: cf_dev2 |
Date: Tuesday, October 23, 2007
|
running two large queries and then joining them in a Cold Fusion query of
queries?
While I realize you're talking about QoQ versus db query/stored procedure, I
would guess any performance you might gain from a QoQ'd be
offset by a
performance
hit caused by creating a new query object and looping row by row
through a very large query.
IMO you should move the
logic out of ColdFusion and put it
into the db where it belongs.
| From: Henweigh99 |
Date: Tuesday, October 23, 2007
|
I think I will need to read a bit more about advancd SQL statements like the one
you posted above in order to understand it better. I'm still not sure how that
query gets all of the values and assigns it to a temporary table. On a side
note, Do you know if Microsoft
Access databases can
accept WHILE EXISTS statements as
well?
If I wanted to use your method on the initial CFQUERY how'd I
write it out?
The table structure looks something like this:
ID, Name, Parent
(where Parent is 0 if it is the top level, or the number value of the ID if it
is a child of that ID)
currently my query looks like:
<CFLOOP condition="ContinueLoop IS 'yes'">
<CFSET LoopCount = LoopCount + 1>
<CFQUERY datasource="#datasource#" name="getMyStuff">
Select ID, Parent
FROM myTable
WHERE Parent = #currentparent#
AND Criteria = '#bla#'
</CFQUERY>
<!-- assign the new parent to a value list and append the master list -->
<CFSET CurrentParent = ValueList(getMyStuff.ID)>
<CFSET RecordsToGet = ListAppend(RecordsToGet,CurrentParent)>
<CFSET loopcount = loopcount + 1>
(continue loop until there are no more records (i.e. you've reached the
bottom of the tree)
</CFLOOP>
| From: cf_dev2 |
Date: Tuesday, October 23, 2007
|
Do you know if Microsoft Access databases can accept WHILE EXISTS statements
I do not know
> If I wanted to use your method on the initial CFQUERY how'd I write it
out
While I know its technically possible, I do not normally use #temp tables
within a cfquery. So I do not know if there are any issues or
concurrency
problems with trying to use that method.
IIRC its not possible using a @xxxxxxxxxxx
variables because of their limited scope. ie. the variables'd only exist
for the life of a cfquery. That said, IMO this logic really belongs in a
stored procedure not a cfquery.
If you've access to Query Analyzer or Enterprise Manager I can post a small
example demonstrating how the sql while loop works if you want.
| From: Henweigh99 |
Date: Tuesday, October 23, 2007
|
I do have Enterprise Manager so your example'd most appreciated.
I will mark your response as the answer to this question while I experiment with
several of the options you've given me. Thanks so much for your help!
| From: cf_dev2 |
Date: Tuesday, October 23, 2007
|
Okay. I will post it as soon as its done.
| From: cf_dev2 |
Date: Tuesday, October 23, 2007
|
Here is an example that shows how the while loop traverses a
folder tree. The
loop starts at the
root and continues until its found all subfolders in the
tree. It displays each level of records as they are being processed.
The hierarchy looks something like this (unimaginative names, I know ;)
Top Folder
-- Produce
----- Apples
----- Oranges
----- Pears
-- Dry Goods
----- Flour
----- Salt
----- Sugar -- ==== RUN ONCE ONLY ====--
--- create
sample table
CREATE TABLE SampleFolderTable
(
FolderID int NOT NULL,
FolderName varchar(100) NOT NULL,
Parent int NOT NULL
)
-- populate with sample values
INSERT INTO SampleFolderTable (FolderID, FolderName, Parent)
SELECT 1, 'Top SampleFolderTable', 0 UNION
SELECT 2, 'Produce', 1 UNION
SELECT 3, 'Dry Goods', 1 UNION
SELECT 4, 'Apples', 2 UNION
SELECT 5, 'Oranges', 2 UNION
SELECT 6, 'Pears', 2 UNION
SELECT 7, 'Flour', 3 UNION
SELECT 8, 'Salt', 3 UNION
SELECT 9, 'Sugar', 3
SET NOCOUNT ON
--- initialize variables/create temp table
DECLARE @xxxxxxxxxxx int
DECLARE @xxxxxxxxxxx int
DECLARE @xxxxxxxxxxx TABLE (
FolderID int,
FolderName varchar(100),
ProcessLevel int
)
SET @xxxxxxxxxxx = 0
SET @xxxxxxxxxxx = 0 -- ie start with root folder (0) --- insert the intial folders to process
--- ie. children of the root
INSERT INTO @xxxxxxxxxxx (FolderID, FolderName, ProcessLevel )
SELECT FolderID, FolderName, @xxxxxxxxxxx
FROM SampleFolderTable
WHERE Parent = @xxxxxxxxxxx /*
While there are folders to process,
loop and get their subfolders ..
*/
WHILE EXISTS ( SELECT 1
FROM @xxxxxxxxxxx
WHERE ProcessLevel = @xxxxxxxxxxx
)
BEGIN
--- show the folders being processed
SELECT 'Debug: Now processing folders', FolderID, FolderName, ProcessLevel
FROM @xxxxxxxxxxx
WHERE ProcessLevel = @xxxxxxxxxxx
--- insert all subfolders
INSERT INTO @xxxxxxxxxxx (FolderID, FolderName, ProcessLevel )
SELECT f.FolderID, f.FolderName, @xxxxxxxxxxx + 1
FROM SampleFolderTable f
INNER JOIN @xxxxxxxxxxx b ON f.Parent = b.FolderID
LEFT JOIN @xxxxxxxxxxx e ON f.FolderID = e.FolderID
WHERE b.ProcessLevel = @xxxxxxxxxxx
AND e.FolderID IS NULL
--- show the subfolders we just inserted
SELECT 'Debug: Finished inserting subfolders ', FolderID, FolderName,
ProcessLevel
FROM @xxxxxxxxxxx
WHERE ProcessLevel = @xxxxxxxxxxx + 1
--- increment the counter, so the next loop will
--- process the subfolders we just inserted
SET @xxxxxxxxxxx = @xxxxxxxxxxx + 1
END
--- now you can use the temp table in a join.
--- example, find all folders with the letter 'A' in them
SELECT f.FolderID, f.FolderName, t.ProcessLevel
FROM SampleFolderTable f
INNER JOIN @xxxxxxxxxxx t ON t.FolderID = f.FolderID
WHERE f.FolderName LIKE '%a%'
SET NOCOUNT OFF
| From: Henweigh99 |
Date: Tuesday, October 23, 2007
|
Thank you! I appreciate you taking the time out of your day to help me.
| From: cf_dev2 |
Date: Tuesday, October 23, 2007
|
You're welcome. Good luck, and if you've any other questions, just let us
know.
Btw, normally you'd only insert the ID's into the temp table. I only
included other columns (ie "FolderName") for demonstration purposes.
Next Message: Calculated Form Fields
Blogs related to Trouble with MSSQL query
Document
... documentation and urosepsis documenter la déchiqueteuse documentsolutionsusa declassified documents about ufo sécurité document unique condition loop though entire xml document in
coldfusion lighthouse document technologies,seattle,
...
Document
... idaho inexpensive document shredding condition loop though entire xml document in
coldfusion copyrighting documents panama dennis quaid documentary windows autobackup documentation reading clarisworks 5.0 documents alfa giulietta
...
Document
264 in 1936 e-smith documentation travel_document plan schedule resource project suning document etc role documented cases of family disowning a homosexual member discplinary documentation uscg vessel documentation noaa
query turabian
...
IT expert exchange: EE Community Newsletter, September 27, 2007
ColdFusion Studio Lotus Notes Email Lotus Notes Email Firefox Windows OS Windows OS Backup Exec DNS Search Engine Optimization (SEO) J2EE Struts Scripting Scripting XHTML Windows Batch SQL Syntax PL / SQL SQL Server 2005 SQL Server 2005
...
Document
... 97012 document documentarios independentes jornalismo condition loop though entire xml document in
coldfusion brl-cad documentation email document management knowledgetree confirmation eurocontrol document sur.et2.st03.2000-id-04
...
Gurus
(via What the heck is he thinking) I've got a
query issue that's driving me crazy Here's the
query, actually it's a SQL server view SELECT TOP (100) PERCENT LEFT(PublicationYear, 4) AS Year, COUNT(PBID) AS BookCount,Category FROM dbo
...