If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Nested SQL Statements causing slowness and errors
I am working on a query for one of my reports. It takes a percentage of
selected diagnoses (and some diagnosis groups). To save the number of queries, I have nested a SQL statement for each diagnosis (diagnosis group). For a single diagnosis, I use the following expression: Primary Dx Schizoaffective Disorder: ((SELECT Count([qryuAxisIDiag].[DiagValue]) AS Count FROM [qryuAxisIDiag] WHERE ((([qryuAxisIDiag].[DiagDetail])="Schizoaffective Disorder")))/[Total Number of Clients.CountOfGender]) For a diagnosis group, I use: Primary Dx PD: ((SELECT Sum([qrygAxisDxPD].[DxCount]) FROM [qrygAxisDxPD])/[Total Number of Clients.CountOfGender]) Which links to another query with the following: SELECT qryuAxisIDiag.ID, IIf(Count([DiagValue])0,1,0) AS DxCount FROM qryuAxisIDiag WHERE (((qryuAxisIDiag.DiagValue)="Personality Disorder")) GROUP BY qryuAxisIDiag.ID; Note that I use the IIf(Count([DiagValue])0,1,0) as (in this case) a patient may have multiple personality disorders, but want to return that a personality disorder is present. Also, qryuAxisIDiag is a union query listing all diagnoses for each patient. In total, there are 26 fields in this query and have found that it runs very slow and often get the "Cannot open any more databases" error. At that point I will have to close and reopen the database. Design view of the report is horribly slow as well *(and sometimes cannot find the controlsource of the fields while in design view). I was hoping to simplify the query, by using Sum() and mathematical expressions instead of the nested SQL statements, but I would get the following error message: "You tried to execute a query that does not include the specified expression 'Sum(Iif(0,1,0))/[CountOfGender]' as part of an aggregate function." Short of creating dozens of additional queries to break up the calculation (which I want to avoid), any ideas on fixing this problem? |
#2
|
|||
|
|||
Nested SQL Statements causing slowness and errors
How many forms are open when you run the query? If you have several
open that have lots of lookup controls then that can contribute to the "cannot open any more databases" failure. That's typically the problem I run into. On Jun 26, 11:07*am, Joel Maxuel wrote: I am working on a query for one of my reports. *It takes a percentage of selected diagnoses (and some diagnosis groups). *To save the number of queries, I have nested a SQL statement for each diagnosis (diagnosis group). * For a single diagnosis, I use the following expression: Primary Dx Schizoaffective Disorder: ((SELECT Count([qryuAxisIDiag].[DiagValue]) AS Count FROM [qryuAxisIDiag] WHERE ((([qryuAxisIDiag].[DiagDetail])="Schizoaffective Disorder")))/[Total Number of Clients.CountOfGender]) For a diagnosis group, I use: Primary Dx PD: ((SELECT Sum([qrygAxisDxPD].[DxCount]) FROM [qrygAxisDxPD])/[Total Number of Clients.CountOfGender]) Which links to another query with the following: SELECT qryuAxisIDiag.ID, IIf(Count([DiagValue])0,1,0) AS DxCount FROM qryuAxisIDiag WHERE (((qryuAxisIDiag.DiagValue)="Personality Disorder")) GROUP BY qryuAxisIDiag.ID; Note that I use the IIf(Count([DiagValue])0,1,0) as (in this case) a patient may have multiple personality disorders, but want to return that a personality disorder is present. *Also, qryuAxisIDiag is a union query listing all diagnoses for each patient. In total, there are 26 fields in this query and have found that it runs very slow and often get the "Cannot open any more databases" error. *At that point I will have to close and reopen the database. *Design view of the report is horribly slow as well *(and sometimes cannot find the controlsource of the fields while in design view). I was hoping to simplify the query, by using Sum() and mathematical expressions instead of the nested SQL statements, but I would get the following error message: "You tried to execute a query that does not include the specified expression 'Sum(Iif(0,1,0))/[CountOfGender]' as part of an aggregate function." Short of creating dozens of additional queries to break up the calculation (which I want to avoid), any ideas on fixing this problem? |
#3
|
|||
|
|||
Nested SQL Statements causing slowness and errors
The only form open is a "Main Menu" which has several buttons, and three
unbound controls, one being a combobox where you can select patients, and two date fields used to report on specific patients that have an admission date between say, Jan 1 and Dec 31 2007. I should add that ID is the primary key, each value being a different patient (and date admitted). qryuAxisIDiag has the following fields: ID DiagOrder - Separates the general Dx type (and which text field the record came from) - does not get used in this example DiagValue - The Diagnosis group (i.e. "Anxiety Disorders") DiagDetail - The specific diagnosis (i.e. "Panic Disorder") |
#4
|
|||
|
|||
Nested SQL Statements causing slowness and errors
What version of Access are you using? I believe pre-2003 versions have
fewer posible connections possible which more readily leads to the cannot open any more db's. I use 2003 and can tell you that this has been a problem for me for quite some time. I recently developed an intensely complicated query that I would say is far more complex than what you've posted. It has 64 columns and LOTS of subqueries and calculations going on - I mean LOTS. I have to run it from my Main Menu, too which has a few more controls than what yours does. Your subquery idea sounds feasible and the error: "You tried to execute a query that does not include the specified expression 'Sum(Iif(0,1,0))/[CountOfGender]' as part of an aggregate function." I believe means that you need to remove Totals from your query - but I could be wrong. Maybe one of the MVP's will confirm. On Jun 26, 11:56*am, Joel Maxuel wrote: The only form open is a "Main Menu" which has several buttons, and three unbound controls, one being a combobox where you can select patients, and two date fields used to report on specific patients that have an admission date between say, Jan 1 and Dec 31 2007. I should add that ID is the primary key, each value being a different patient (and date admitted). *qryuAxisIDiag has the following fields: ID DiagOrder - Separates the general Dx type (and which text field the record came from) - does not get used in this example DiagValue - The Diagnosis group (i.e. "Anxiety Disorders") DiagDetail - The specific diagnosis (i.e. "Panic Disorder") |
#5
|
|||
|
|||
Nested SQL Statements causing slowness and errors
I use Access 2003, but the database is saved in 2000 format (to be more
compatible for the clients that will use it). Just for fun, I tried out the report in Access 2000, and I don't get the crippling error message, however it still takes 12 seconds to load (split database, front end on local harddrive, back end on network share). As a comparison, opening a form with over 100 bound controls takes less than a second. " wrote: What version of Access are you using? I believe pre-2003 versions have fewer posible connections possible which more readily leads to the cannot open any more db's. I use 2003 and can tell you that this has been a problem for me for quite some time. I recently developed an intensely complicated query that I would say is far more complex than what you've posted. It has 64 columns and LOTS of subqueries and calculations going on - I mean LOTS. I have to run it from my Main Menu, too which has a few more controls than what yours does. Your subquery idea sounds feasible and the error: "You tried to execute a query that does not include the specified expression 'Sum(Iif(0,1,0))/[CountOfGender]' as part of an aggregate function." I believe means that you need to remove Totals from your query - but I could be wrong. Maybe one of the MVP's will confirm. The interesting part is that if you take out the denominator the query calculates without error. This gives me an idea. I may try calculating the percentage as a separate query or even as an expression in each report control. Will follow up one this. |
#6
|
|||
|
|||
Nested SQL Statements causing slowness and errors
I think I've got it. I moved the percentage expressions to the report, thus
avioding the aggregate error messages. For each diagnosis being used in the report, I broke apart the nested SQL statements and replaced them with something like "IIf([DiagDetail]="Schizoaffective Disorder",1,0)". I threw the ID field in the query as well. I then created another query that ran the sum of each field of the previous query (the raw data of the firsgt had 6 lines for each ID, this query wraps it up to 1 per ID). Finally, I added the fields to the main query with the dianosis groups (mentioned in the previous posts) where everything is added together. All in all, the report load time decreased from 12 seconds to 4 seconds, and have not experienced the "cannot open any more databases" error since. The only limitation now is that modifying the report in design view is still lagging. This won't be much of a problem as I won't be editing this much, but I do recall broken reports can be fixed by exporting its configuration to a text file, deleting the report, and recreating it with the text file. Is anyone familiar with this? |
#7
|
|||
|
|||
Nested SQL Statements causing slowness and errors
Glad to see that you got it!
Hopefully someone can help you with your other question. On Jun 27, 8:53*am, Joel Maxuel wrote: I think I've got it. *I moved the percentage expressions to the report, thus avioding the aggregate error messages. *For each diagnosis being used in the report, I broke apart the nested SQL statements and replaced them with something like *"IIf([DiagDetail]="Schizoaffective Disorder",1,0)". *I threw the ID field in the query as well. *I then created another query that ran the sum of each field of the previous query (the raw data of the firsgt had 6 lines for each ID, this query wraps it up to 1 per ID). *Finally, I added the fields to the main query with the dianosis groups (mentioned in the previous posts) where everything is added together. All in all, the report load time decreased from 12 seconds to 4 seconds, and have not experienced the "cannot open any more databases" error since. The only limitation now is that modifying the report in design view is still lagging. *This won't be much of a problem as I won't be editing this much, but I do recall broken reports can be fixed by exporting its configuration to a text file, deleting the report, and recreating it with the text file. *Is anyone familiar with this? |
#8
|
|||
|
|||
Nested SQL Statements causing slowness and errors
I was able to find te VBA script that exports and imports reports via text
files, and the performance has not changed. I also looked into a persitent connection (found http://www.granite.ab.ca/access/perf...dblocking.htm), however did not notice any change. I have a combo box on my main menu that brings up a list of patients so that may have been my persistent connection all along. " wrote: Glad to see that you got it! Hopefully someone can help you with your other question. |
Thread Tools | |
Display Modes | |
|
|