hi,<br> Im developing using Coldfusion and Im using an SQL<br>database. Now, part of the code requires me to access/query<br>the database several times ...<br> Now Im thinking if 30-40 people run this same code <br>at the same time ... will it affect the SQL database server in such a way that it will give an out of memory error and<br>crash ???<br><br>thx<br>F/S
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can too many query requests crash SQL ?
- Thread starter firesign
- Start date
Each person will have a session on the database which takes up a small amount of memory per connection. The SQL Server will handle any queueing that the queries require, that is it's job in life.<br><br>If you have any problems, you are more likely to get table locking contentions than server out of memory. As always this would be a 'that depends' type question. It depends how much data each query is requesting, how the database is designed, etc. <p> <br><a href=mailto: > </a><br><a href= home</a><br>
- Thread starter
- #3
The main error that I get is a <br><br>ODBC error = 37000<br>not enough memory to complete this query.<br><br>Someone said that the code may be running into an infinte<br>loop ...<br><br>F/S <p>Vince<br><a href=mailto:firesign1@altavista.net>firesign1@altavista.net</a><br><a href= home for all the Auctions.</a><br><br>
(----- Great place for Auctions -----)
(----- Great place for Auctions -----)
Can you show us the code in question? Is it a single line of SQL....<br><br>A classic mistake is to mention a table in the 'from' clause and not link it in the 'where' causing a cartesian query, making a massive result set and running out of memory. <p> <br><a href=mailto: > </a><br><a href= home</a><br>
- Thread starter
- #6
Hi, <br> I dont know if this would make any sense...<br>But in brief ... there is the first query that picks up<br>information from the categories database. Then there are<br>loops that check each item that is queried, and cross reference check thru another query.<br><br> Basically, lot of queries and lot of loops.<br>And 100+ ppl will be running this at any given point of time. And within matter of hours the server is down!<br><br>F/S<br><br><br> <br>=====================================================================<br>.....<br><cfsetting enablecfoutputonly="Yes"><br><cfset plist = ""><br><!--- get categories information ---><br><br><cfquery NAME="get_CategoryInfo" DATASOURCE="#DATASOURCE#"><br>SELECT parent, name, date_created, allow_sales<br>FROM categories<br>WHERE category = #category#<br></cfquery><br><br><!--- if category not found, redirect ---><br><cfif not get_CategoryInfo.RecordCount><br><cflocation url="<A HREF=" TARGET="_new"> if parent is 0 or RecordCount is 0 ---><br><cfif category IS 0 OR get_CategoryInfo.RecordCount IS 0><br><cfset parent_available = "FALSE"><br><cfset category_name = "Top"><br><cfset public_sales = "FALSE"><br><cfelse><br><cfset parent_available = "TRUE"><br><cfset category_name = Trim(get_CategoryInfo.name)><br><cfset public_sales = get_CategoryInfo.allow_sales><br><br><!--- look up parents ---><br><cfmodule TEMPLATE="../functions/parentlookup.cfm"<br>CATEGORY= "#category#"<br>DATASOURCE="#DATASOURCE#"<br>RETURN="parents_array"><br></cfif><br><br><!--- get category_new value ---><br><cfquery NAME="CategoryNew" DATASOURCE="#DATASOURCE#"><br>SELECT pair AS days<br>FROM defaults<br>WHERE name = 'category_new'<br></cfquery><br><br><!--- get number of auctions in parent catgory ---><br><br><CFMODULE TEMPLATE="tree.cfm"<br>TYPE="RETRIEVE"<br>DATASOURCE="#DATASOURCE#"<br>PARENT=#category#<br>return="result"<br>REQUIRE_LOGIN="0"><br><br><!--- Set delimiter variables ---><br><cfset #int_delim# = "÷"><br><cfset #ext_delim# = "²"><br><br><cfoutput><TABLE BORDER=0 CELLSPACING=2 CELLPADDING=0 NOSHADE></cfoutput><br><cfsetting enablecfoutputonly="YES"><br><br><cfloop index="l" list="#result#" delimiters="#ext_delim#"><br><cfset totalfound = 0><br><cfif Evaluate(ListGetAt(l, 3, int_delim) + 1) LTE Variables.levelsDisplayed><br><br><!--- get catinfo ---><br><cfquery name="getCatInfo" datasource="#DATASOURCE#"><br>SELECT date_created, allow_sales<br>FROM categories<br>WHERE category = #ListGetAt(l, 1, int_delim)# <br></cfquery><br><!--- parent total child count ---><br><cfquery name="ParentTotal" datasource="#DATASOURCE#"><br>SELECT child_count from categories<br>where category = #ListGetAt(l, 1, int_delim)#<br></cfquery><br><br><!-- Check to see if the Parent has kids ---><br><!--- ***** starting level one check ***** ---><br><cfset levelone = "false"> <br><cfset levelonea = "false"> <br><cfif ListGetAt(l, 4, int_delim) GT 0 and ListGetAt(l, 3, int_delim) EQ 0><br><cfset Vlist = ""><br><!--- get the kid category nos ---><br><cfquery name="check1" datasource="#DATASOURCE#"><br>SELECT category as CateChk<br>FROM categories<br>WHERE parent = #ListGetAt(l, 1, int_delim)#<br></cfquery><br><br><!--- dump that into the list ---> <br><cfloop query="check1"><br><cfset #Vlist# = #listAppend (Vlist,"#CateChk#"
#><br></cfloop> <br><br><!--- search the list for the child category having children ---><br><cfloop index="aa" list="#vlist#"><br><br><cfquery name="check2" datasource="#DATASOURCE#"><br>SELECT category as CateChk1 from categories<br>WHERE parent = #aa#<br></cfquery> <br><br><!--- *** LeveloneA check *** ---><br><cfif #check2.recordcount# EQ 0><br><br><!--- get all the items that match callist from ITEMS ---><br><cfquery name="GetSearch" datasource="#DATASOURCE#"><br>SELECT COUNT(itemnum) AS found<br>FROM items<br>WHERE (category1 = #aa# OR category2 = #aa#) <br>AND status = 1<br>AND date_start < #TIMENOW#<br>AND date_end > #TIMENOW# <br></cfquery> <br><br><cfset totalfound = totalfound + #Getsearch.found#> <br><cfset levelonea = "true"> <br></cfif><br><br><!--- dump categories in to calist ---><br><cfif levelonea EQ "false"><br><cfset calist=""><br><cfloop query="check2"><br><cfset #calist# = #listAppend (calist,"#CateChk1#"#><br></cfloop> <br><br><!--- get all the items that match callist from ITEMS ---><br><cfloop index="ab" from="1" to="#check2.recordcount#"><br><cfquery name="itemGet" datasource="#DATASOURCE#"><br>SELECT COUNT(itemnum) AS found<br>FROM items<br>WHERE (category1 = #listGetAt(calist,ab)# OR category2 = #listGetAt(calist,ab)#) <br>AND status = 1<br>AND date_start < #TIMENOW#<br>AND date_end > #TIMENOW# <br></cfquery> <br><cfset totalfound = totalfound + #itemGet.found#> <br><cfset levelone = "true"> <br></cfloop><br></cfif><br><br></cfloop><br></cfif><br><br><!--- now for the second level ---><br><cfset leveltwo = "false"><br><cfif ListGetAt(l, 4, int_delim) GT 0 and ListGetAt(l, 3, int_delim) GT 0 and #levelone# EQ "false" and #levelonea# EQ "false"> <br><cfset valist = ""><br><br><!--- get all the categories for the second level ---><br><cfquery name="getC2" datasource="#DATASOURCE#"><br>SELECT category as cchk<br>from categories<br>WHERE parent = #ListGetAt(l, 1, int_delim)#<br></cfquery><br><br><!--- make a list of it ---><br><cfloop query="getC2"><br><cfset #Valist# = #listAppend (Valist,"#CChk#"#><br></cfloop> <br><br><!--- now do the check ---><br><cfloop index="ab1" list="#Valist#"><br><cfquery name="itemGets" datasource="#DATASOURCE#"><br>SELECT COUNT(itemnum) AS found<br>FROM items<br>WHERE (category1 = #ab1# OR category2 = #ab1#) <br>AND status = 1<br>AND date_start < #TIMENOW#<br>AND date_end > #TIMENOW# <br></cfquery> <br><cfset totalfound = totalfound + #itemGets.found#> <br><br><cfset leveltwo = "true"> <br></cfloop><br></cfif><br><br><br><!--- now total the third level ---><br><cfset levelthree = "false"><br><cfif ListGetAt(l, 4, int_delim) EQ 0 and #levelone# EQ "false" and #leveltwo# EQ "false" and #levelonea# EQ "false"> <br><cfquery name="getC" datasource="#DATASOURCE#"><br>SELECT COUNT(itemnum) AS total_auctions<br>FROM items<br>WHERE (category1 = #ListGetAt(l, 1, int_delim)#<br>OR category2 = #ListGetAt(l, 1, int_delim)#) <br></cfquery><br><cfset totalfound = totalfound + #getc.total_auctions#><br><cfset levelthree = "true"><br></cfif><br><br><!--- the final output comes here ---> <br>=======================================================================<br> <p> <br><a href=mailto: > </a><br><a href=
#><br></cfloop> <br><br><!--- search the list for the child category having children ---><br><cfloop index="aa" list="#vlist#"><br><br><cfquery name="check2" datasource="#DATASOURCE#"><br>SELECT category as CateChk1 from categories<br>WHERE parent = #aa#<br></cfquery> <br><br><!--- *** LeveloneA check *** ---><br><cfif #check2.recordcount# EQ 0><br><br><!--- get all the items that match callist from ITEMS ---><br><cfquery name="GetSearch" datasource="#DATASOURCE#"><br>SELECT COUNT(itemnum) AS found<br>FROM items<br>WHERE (category1 = #aa# OR category2 = #aa#) <br>AND status = 1<br>AND date_start < #TIMENOW#<br>AND date_end > #TIMENOW# <br></cfquery> <br><br><cfset totalfound = totalfound + #Getsearch.found#> <br><cfset levelonea = "true"> <br></cfif><br><br><!--- dump categories in to calist ---><br><cfif levelonea EQ "false"><br><cfset calist=""><br><cfloop query="check2"><br><cfset #calist# = #listAppend (calist,"#CateChk1#"#><br></cfloop> <br><br><!--- get all the items that match callist from ITEMS ---><br><cfloop index="ab" from="1" to="#check2.recordcount#"><br><cfquery name="itemGet" datasource="#DATASOURCE#"><br>SELECT COUNT(itemnum) AS found<br>FROM items<br>WHERE (category1 = #listGetAt(calist,ab)# OR category2 = #listGetAt(calist,ab)#) <br>AND status = 1<br>AND date_start < #TIMENOW#<br>AND date_end > #TIMENOW# <br></cfquery> <br><cfset totalfound = totalfound + #itemGet.found#> <br><cfset levelone = "true"> <br></cfloop><br></cfif><br><br></cfloop><br></cfif><br><br><!--- now for the second level ---><br><cfset leveltwo = "false"><br><cfif ListGetAt(l, 4, int_delim) GT 0 and ListGetAt(l, 3, int_delim) GT 0 and #levelone# EQ "false" and #levelonea# EQ "false"> <br><cfset valist = ""><br><br><!--- get all the categories for the second level ---><br><cfquery name="getC2" datasource="#DATASOURCE#"><br>SELECT category as cchk<br>from categories<br>WHERE parent = #ListGetAt(l, 1, int_delim)#<br></cfquery><br><br><!--- make a list of it ---><br><cfloop query="getC2"><br><cfset #Valist# = #listAppend (Valist,"#CChk#"#><br></cfloop> <br><br><!--- now do the check ---><br><cfloop index="ab1" list="#Valist#"><br><cfquery name="itemGets" datasource="#DATASOURCE#"><br>SELECT COUNT(itemnum) AS found<br>FROM items<br>WHERE (category1 = #ab1# OR category2 = #ab1#) <br>AND status = 1<br>AND date_start < #TIMENOW#<br>AND date_end > #TIMENOW# <br></cfquery> <br><cfset totalfound = totalfound + #itemGets.found#> <br><br><cfset leveltwo = "true"> <br></cfloop><br></cfif><br><br><br><!--- now total the third level ---><br><cfset levelthree = "false"><br><cfif ListGetAt(l, 4, int_delim) EQ 0 and #levelone# EQ "false" and #leveltwo# EQ "false" and #levelonea# EQ "false"> <br><cfquery name="getC" datasource="#DATASOURCE#"><br>SELECT COUNT(itemnum) AS total_auctions<br>FROM items<br>WHERE (category1 = #ListGetAt(l, 1, int_delim)#<br>OR category2 = #ListGetAt(l, 1, int_delim)#) <br></cfquery><br><cfset totalfound = totalfound + #getc.total_auctions#><br><cfset levelthree = "true"><br></cfif><br><br><!--- the final output comes here ---> <br>=======================================================================<br> <p> <br><a href=mailto: > </a><br><a href=- Thread starter
- #7
I figured this code would be to long for anyone to read! : )<br> <p> <br><a href=mailto: > </a><br><a href=
- Thread starter
- #9
OK !!<br> This is exactly what I was told ... that some part of the code was going into and infinite loop.<br>But for some reason no one could detect that particular page that does this ...<br> How would i know which part of the code goes into and infinte loop. All the loops are in for a specific length.<br><br> Is there a way of finding out where this might be happening ??? Cause I have the site on a test server here.<br>I also have my task manager opened up. So I can see the activity that takes place when ever there is a query<br>run. And yeah, for a brief 2 seconds there is the CPU<br>utilization is at 100% and then fades away to a steady 9%.<br><br> So, if there is some form of activity taking place due to the infinite loop, I would know ....right !<br><br>WHAT DO I DO NOW ??<br><br>regards,<br>F/S<br><br> <p> <br><a href=mailto: > </a><br><a href=
Use SQL profiler to watch what sql is being sent to the db from your app. <br><br>Often what you code can actually end up sending many request to the backend i.e. cursors can cause to network passes for each record requested from a recordset... <p>Cal<br><a href=mailto: > </a><br><a href= > </a><br>
- Status
Similar threads
- Locked
- Question
- Locked
- Question