Can too many query requests crash SQL ?

firesign · May 9, 2000

hi,   Im developing using Coldfusion and Im using an SQL database. Now, part of the code requires me to access/query the database several times ...   Now Im thinking if 30-40 people run this same code 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 crash ??? thx F/S

jnicho02 · May 9, 2000

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. 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. <a href=mailto: > </a> <a href=

http://www.geocities.com/jnicho02/>my

home</a>

firesign · May 9, 2000

The main error that I get is a ODBC error = 37000 not enough memory to complete this query. Someone said that the code may be running into an infinte loop ... F/S Vince <a href=mailto:firesign1@altavista.net>firesign1@altavista.net</a> <a href=

http://www.auctionhome.net>The

home for all the Auctions.</a> 
(----- Great place for Auctions -----)

calahans · May 9, 2000

I would imagine that the ODBC error relates to the client machine. Bill Gates would cringe if he thought that you where intimating that SQL Server could not handle over 40+ users... Cal <a href=mailto: > </a> <a href= > </a>

jnicho02 · May 9, 2000

Can you show us the code in question? Is it a single line of SQL.... 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. <a href=mailto: > </a> <a href=

http://www.geocities.com/jnicho02/>my

home</a>

firesign · May 10, 2000

Hi,   I dont know if this would make any sense... But in brief ... there is the first query that picks up information from the categories database. Then there are loops that check each item that is queried, and cross reference check thru another query.    Basically, lot of queries and lot of loops. And 100+ ppl will be running this at any given point of time. And within matter of hours the server is down! F/S    ===================================================================== ..... <cfsetting enablecfoutputonly="Yes"> <cfset plist = "">  <cfquery NAME="get_CategoryInfo" DATASOURCE="#DATASOURCE#"> SELECT parent, name, date_created, allow_sales FROM categories WHERE category = #category# </cfquery>  <cfif not get_CategoryInfo.RecordCount> <cflocation url="<A HREF="

http://#CGI.SERVER_NAME#/includes/404notfound.cfm&quot"

TARGET="_new">

http://#CGI.SERVER_NAME#/includes/404notfound.cfm&quot</A>;> </cfif> <!---

if parent is 0 or RecordCount is 0 ---> <cfif category IS 0 OR get_CategoryInfo.RecordCount IS 0> <cfset parent_available = "FALSE"> <cfset category_name = "Top"> <cfset public_sales = "FALSE"> <cfelse> <cfset parent_available = "TRUE"> <cfset category_name = Trim(get_CategoryInfo.name)> <cfset public_sales = get_CategoryInfo.allow_sales>  <cfmodule TEMPLATE="../functions/parentlookup.cfm" CATEGORY= "#category#" DATASOURCE="#DATASOURCE#" RETURN="parents_array"> </cfif>  <cfquery NAME="CategoryNew" DATASOURCE="#DATASOURCE#"> SELECT pair AS days FROM defaults WHERE name = 'category_new' </cfquery>  <CFMODULE TEMPLATE="tree.cfm" TYPE="RETRIEVE" DATASOURCE="#DATASOURCE#" PARENT=#category# return="result" REQUIRE_LOGIN="0">  <cfset #int_delim# = "÷"> <cfset #ext_delim# = "²"> <cfoutput><TABLE BORDER=0 CELLSPACING=2 CELLPADDING=0 NOSHADE></cfoutput> <cfsetting enablecfoutputonly="YES"> <cfloop index="l" list="#result#" delimiters="#ext_delim#"> <cfset totalfound = 0> <cfif Evaluate(ListGetAt(l, 3, int_delim) + 1) LTE Variables.levelsDisplayed>  <cfquery name="getCatInfo" datasource="#DATASOURCE#"> SELECT date_created, allow_sales FROM categories WHERE category = #ListGetAt(l, 1, int_delim)# </cfquery>  <cfquery name="ParentTotal" datasource="#DATASOURCE#"> SELECT child_count from categories where category = #ListGetAt(l, 1, int_delim)# </cfquery>   <cfset levelone = "false"> <cfset levelonea = "false"> <cfif ListGetAt(l, 4, int_delim) GT 0 and ListGetAt(l, 3, int_delim) EQ 0> <cfset Vlist = "">  <cfquery name="check1" datasource="#DATASOURCE#"> SELECT category as CateChk FROM categories WHERE parent = #ListGetAt(l, 1, int_delim)# </cfquery>  <cfloop query="check1"> <cfset #Vlist# = #listAppend (Vlist,"#CateChk#&quot

#> </cfloop>  <cfloop index="aa" list="#vlist#"> <cfquery name="check2" datasource="#DATASOURCE#"> SELECT category as CateChk1 from categories WHERE parent = #aa# </cfquery>  <cfif #check2.recordcount# EQ 0>  <cfquery name="GetSearch" datasource="#DATASOURCE#"> SELECT COUNT(itemnum) AS found FROM items WHERE (category1 = #aa# OR category2 = #aa#) AND status = 1 AND date_start < #TIMENOW# AND date_end > #TIMENOW# </cfquery> <cfset totalfound = totalfound + #Getsearch.found#> <cfset levelonea = "true"> </cfif>  <cfif levelonea EQ "false"> <cfset calist=""> <cfloop query="check2"> <cfset #calist# = #listAppend (calist,"#CateChk1#&quot

#> </cfloop>  <cfloop index="ab" from="1" to="#check2.recordcount#"> <cfquery name="itemGet" datasource="#DATASOURCE#"> SELECT COUNT(itemnum) AS found FROM items WHERE (category1 = #listGetAt(calist,ab)# OR category2 = #listGetAt(calist,ab)#) AND status = 1 AND date_start < #TIMENOW# AND date_end > #TIMENOW# </cfquery> <cfset totalfound = totalfound + #itemGet.found#> <cfset levelone = "true"> </cfloop> </cfif> </cfloop> </cfif>  <cfset leveltwo = "false"> <cfif ListGetAt(l, 4, int_delim) GT 0 and ListGetAt(l, 3, int_delim) GT 0 and #levelone# EQ "false" and #levelonea# EQ "false"> <cfset valist = "">  <cfquery name="getC2" datasource="#DATASOURCE#"> SELECT category as cchk from categories WHERE parent = #ListGetAt(l, 1, int_delim)# </cfquery>  <cfloop query="getC2"> <cfset #Valist# = #listAppend (Valist,"#CChk#&quot

#> </cfloop>  <cfloop index="ab1" list="#Valist#"> <cfquery name="itemGets" datasource="#DATASOURCE#"> SELECT COUNT(itemnum) AS found FROM items WHERE (category1 = #ab1# OR category2 = #ab1#) AND status = 1 AND date_start < #TIMENOW# AND date_end > #TIMENOW# </cfquery> <cfset totalfound = totalfound + #itemGets.found#> <cfset leveltwo = "true"> </cfloop> </cfif>  <cfset levelthree = "false"> <cfif ListGetAt(l, 4, int_delim) EQ 0 and #levelone# EQ "false" and #leveltwo# EQ "false" and #levelonea# EQ "false"> <cfquery name="getC" datasource="#DATASOURCE#"> SELECT COUNT(itemnum) AS total_auctions FROM items WHERE (category1 = #ListGetAt(l, 1, int_delim)# OR category2 = #ListGetAt(l, 1, int_delim)#) </cfquery> <cfset totalfound = totalfound + #getc.total_auctions#> <cfset levelthree = "true"> </cfif>  ======================================================================= <a href=mailto: > </a> <a href=

http://www.auctionhome.net>Auctionhome</a>

firesign · May 12, 2000

I figured this code would be to long for anyone to read! : ) <a href=mailto: > </a> <a href=

http://www.auctionhome.net>Auctionhome</a>

991973 · May 20, 2000

Too many requests cannot crash the Sql server reason being that if u r code goes into infinite loop the cpu utilisation will be 100% and if it persists for along time the conncection will be severed.

firesign · May 20, 2000

OK !!  This is exactly what I was told ... that some part of the code was going into and infinite loop. But for some reason no one could detect that particular page that does this ...    How would i know which part of the code goes into and infinte loop. All the loops are in for a specific length.   Is there a way of finding out where this might be happening ??? Cause I have the site on a test server here. I also have my task manager opened up. So I can see the activity that takes place when ever there is a query run. And yeah, for a brief 2 seconds there is the CPU utilization is at 100% and then fades away to a steady 9%.   So, if there is some form of activity taking place due to the infinite loop, I would know ....right ! WHAT DO I DO NOW ?? regards, F/S <a href=mailto: > </a> <a href=

http://www.auctionhome.net>Auctionhome</a>

calahans · May 21, 2000

Use SQL profiler to watch what sql is being sent to the db from your app. 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... Cal <a href=mailto: > </a> <a href= > </a>

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!

Can too many query requests crash SQL ?

firesign

Programmer

jnicho02

Programmer

firesign

Programmer

calahans

Programmer

jnicho02

Programmer

firesign

Programmer

firesign

Programmer

991973

Programmer

firesign

Programmer

calahans

Programmer

Similar threads

Part and Inventory Search

Sponsor