SQL Problem

[matstone98]

I have spent the last 6 months working on a database system to replace my companies current Microsoft Access database with a coldfusion based one. The backend is still Access and we have previously been using a coldfusion pageset to export data to a spreadsheet with no problems, with a slight re-write to the SQL statement that retrieves the data we have an expanded version that sends required information to a spreadsheet, which was also working without problems until last week.

Now everytime we run a query whether its complicated or simple, the server hangs whilst coldfusion takes up all its resources, and connections timeout when trying to load coldfusion pages. I have tried using an alternative server thinking it was a problem with the machine but exactly the same problem occurs.

I am at my wits end as to why this has started happening and this system is the lifeblood of our company. So unless I can fix it I will be looking for a new job! :(

Any help, critiscms, feedback etc will be greatly appreciated.

[ebdesigns]

<cfquery name="Export_Contacts" datasource="idv6" maxrows="#MaximumRecords#">
SELECT * FROM companies, source, status WHERE
<cfif NOT IsDefined('AllAreas')>
(Postcode LIKE 'ZZ%' OR

the WHERE is before
if not isdefined('all areas')

you could add <cfelse> 1=1
or just move the WHERE inside that condition

[matstone98]

That part of the code dictates whether or not to include individual postcode filters on the query. If ALLAREAS is defined then it bypasses the postcode filtering completly and moves onto the next set of AND n' OR statements.

So i don't think moving the WHERE would solve the problem in this case 🙂

[Kapitaine]

If you just do:

<cfquery name="Export_Contacts" datasource="idv6" maxrows="#MaximumRecords#">
SELECT * FROM companies
</cfquery>

Does it still hang? If not then it's your query, if so, then you know the problem is much deeper.

Also, I have noticed you have two conditions that meet the same requirement - but do different things...

<cfif exporttype IS 'NowDelete'>AND dateupdated > #today2#</cfif>
<cfif exporttype IS 'NowDelete'>AND status = 6</cfif>

Do these conflict somehow? If not - you may aswell be putting them in the same condition ( <cfif> )

<cfif exporttype IS 'NowDelete'>AND dateupdated > #today2# AND status = 6</cfif>

[ebdesigns]

Try executing this and cfdump the results to make sure your basic
query still works

SELECT * FROM companies, source, status

[Kapitaine]

Oh and...are you sure the columns you are testing / doing conditions against exist in all three tables? In your select you are getting three tables and testing the columns values - but do all three tables have these columns?

I'm no expert, just something I noticed.

[ebdesigns]

turn debugging on and cut and paste an acutal query that is executed in your template so we can see what is actually executed.

Also you can paste that in your query analyzer to test it there.

[The_ScareCrow]

My 2c

it's a bad idea to use Select *
This can cause problems if you have the same column name in m ore than one table.
The query will also be more effiecent if you list all the columns that are required (only list the columns that you are going to use). Also list any varchar or text columns after integer columns. That is from smallest to largest.
As ebdesigns has said.
If all those if conditions are not true then your query will fail because there will be an invalid where clause.

Ken

[Magikaru]

I'm no sql expert so correct me if I'm wrong, guys.

1. You're pulling data from three different tables. I would imagine that it would increase the speed if you state in the query which field is in which table so sql doesn't have to go figure that out on its own. ex: use status.INSTalu = 'yes' instead of INSTalu = 'yes'

2. Perhaps consider breaking your huge query into a few smaller queries. It's worked for me once before.

3. Try pulling everything from those three tables in one query and then using a query of queries to get the data you are looking for. Wouldn't this cause less stress on the database?

[matstone98]

Thanks for everyones input. I have re-written the entire page from scratch and reduced the number of columns being used in this query as well as alot of the suggestions above.

Now everything is working fine again!

[The_ScareCrow]

I thought I should respond to Magikaru's post.

1 No, it really does not make that much difference as you could also give the tables an alias

2. I don't really agree with this concept as it may result in inconsistant/incorrect data

3. Pulling everything from the database and then manuipulating the result set in CF is not effiecent. As you would be returning data that may never be used. Then using QofQ increases the complexity when it's not needed.

In general the database stuff should be left to the database. When dealling with ms access this is not possible.
But if you have a database server, then the interaction to the data should be done on the database server. With as little calls to the database as possible.

If you need to start manipulating queries, then I would suggest you need to look at a better database solution.

Ken

[-__cfSearching__-]

matstone98,

I am glad you redesigned and that everything is working now.

But I think it is worth mentioning that the original query code was way too long. When you see a few hundred lines of cfif conditions in a query, that should raise a red flag. Time to re-think and redesign. Having that many conditions makes the code unmanageable. In this case it also obscured the fact that the final query appears to be structured incorrectly. When none of the variables exist, the final query is

SELECT * FROM companies, source, status
WHERE (Postcode LIKE 'ZZ%' OR Postcode LIKE 'ZX%')
AND (INSTpvcu = 'start' OR FABother = 'finish')
OR INSTALL = '#INSTALL#'
OR TRADECOUNTER = '#TRADECOUNTER#'
AND companies.source = source.sourceid
AND companies.status = status.statusid

Notice the WHERE clause conditions are not grouped properly. So the size of resultset could potentially be quite large. Not to mention the results being completely inaccurate because the conditions are not evaluated in the proper order.