Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 7 of 7 total |
SELECT Queries causing locking |
Mon, Sep 29 2008 5:54 PM | Permanent Link |
Michael | DBISAM v4.25 Build 6.
We have reports that take from 1 to 5 minutes to run. The report runs a single SQL Script that contains about 4 actual SQL selects. The first three are populating memory tables and the last select is combining all the results. They have 4 users all setup in Client-Server mode and the database on a dedicated dual processor server with 4gb of ram. Everything works very fast until someone starts one of these reports. As soon as someone starts a report, all other users are blocked from making any edits until the report is finished. That means they are sitting there with an hourglass for 1 to 5 minutes. As soon as the report is finished, all waiting transactions go through just fine. The SQL Script is about 99% optimized according to the "Generate Plan" results in DBSYS. It is a complicated SQL with a lot of tables and I'm not worried about the 1 to 5 minutes the report is taking. I'm just worried about the other users being locked from making edits. The customer is about to bail and purchase another program. Does anyone know how to improve this or what I might be doing wrong? -- Thanks, Michael B |
Tue, Sep 30 2008 5:56 AM | Permanent Link |
"Eduardo [HPro]" | Michael
Could you post the SQL statement here ? If so, we can help you to find some other option to perform the same task. Eduardo |
Tue, Sep 30 2008 8:27 AM | Permanent Link |
Michael | Eduardo,
It isn't one specific SQL statement. The entire custom reporting engine lets our users select different query parameters, report settings, etc. Based on the users input we generate the needed SQL and present a report to the user through our report writer (ACE by SCTAssociates). There are thousands of combinations for these reports so posting one isn't going to help much. They are just select statements, no updating. They range from a single SQL SELECT to multiple SQL SELECTs submitted to the server in one query as a script. They take anywhere from 1 to 5 minutes to run but are almost all 100% optimized according to the "Generate Plan". They just take a while because they are going through a couple hundred thousand records with a lot of joins. Is it normal for all edit/update actions to be blocked while doing a select query? That doesn't make a ton of sense and I've not seen that with the other database systems I've used (SQL Server, Firebird, SqlAnywhere). -- Michael "Eduardo [HPro]" <contato@hpro.com.br> wrote: Michael Could you post the SQL statement here ? If so, we can help you to find some other option to perform the same task. Eduardo |
Tue, Sep 30 2008 8:42 AM | Permanent Link |
Fernando Dias Team Elevate | Michael,
Are the queries being executed under a transaction ? -- Fernando Dias [Team Elevate] |
Tue, Sep 30 2008 12:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Michael,
<< They are just select statements, no updating. They range from a single SQL SELECT to multiple SQL SELECTs submitted to the server in one query as a script. They take anywhere from 1 to 5 minutes to run but are almost all 100% optimized according to the "Generate Plan". They just take a while because they are going through a couple hundred thousand records with a lot of joins. Is it normal for all edit/update actions to be blocked while doing a select query? That doesn't make a ton of sense and I've not seen that with the other database systems I've used (SQL Server, Firebird, SqlAnywhere). >> No, DBISAM does not block writers when executing a query, as long as the query is optimized. If you're seeing readers blocking writers, then you've got an issue with a query executing an entire table scan, or close to it, which will cause a read lock to be held on the table for most of the duration of the scan. As Eduardo has pointed out, there's no way we can help you further without seeing at least an execution plan of a query that is having this issue. Generalities don't really help since they don't give us any specific information that we can comment on. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Sep 30 2008 4:44 PM | Permanent Link |
Michael | Here is the plan. Almost near the bottom is one UN-OPTIMIZED scan selection on
trans_type = 0 OR NOT ta_linkto_trans IS NULL There is an index on trans_type and ta_linkto_trans is from a table that is connected through an outer join. Would this be the cause and if so, how can that statement be optimized? There are other SQL statements that look like they could be eliminated completely but remember that the entire SQL is generated from a set of components that is taking into consideration multiple user inputs and those components are used for all reports. ================================================================================ SQL statement (Executed with 4.25 Build 6) ================================================================================ SELECT b1.bp_internal_id , b1.bp_linkto_pa , b1.bp_type INTO MEMORY\bp_filter FROM billing_parties b1 INNER JOIN billing_parties b2_pa ON b1.bp_linkto_pa = b2_pa.bp_linkto_pa WHERE (b2_pa.bp_level = 0) AND ((b1.bp_type = 0) OR (b1.bp_type = 1) OR ((b1.bp_type = 2) AND (b1.bp_level in (2)))) AND (b2_pa.bp_linkto_th IN (84)) AND (b2_pa.bp_type = 0) GROUP BY b1.bp_internal_id Tables Involved --------------- billing_parties (b1) table opened shared, has 18794 rows billing_parties (b2_pa) table opened shared, has 18794 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more grouped rows Result set will be grouped by the following column(s) using a temporary index: bp_internal_id Result set will be ordered by the temporary index created for the grouping WHERE Clause Execution ---------------------- The expression: b2_pa.bp_level = 0 is OPTIMIZED, covers 11940 rows or index keys, costs 108654 bytes, and will be applied to the billing_parties table (b2_pa) before any joins The expression: b1.bp_type = 0 OR b1.bp_type = 1 OR b1.bp_type = 2 AND b1.bp_level in (2) is PARTIALLY-OPTIMIZED, covers 9755 rows or index keys, costs 278992 bytes, and will be applied to the billing_parties table (b1) before any joins The expression: b2_pa.bp_linkto_th IN (84) is OPTIMIZED, covers 96 rows or index keys, costs 6614 bytes, and will be applied to the billing_parties table (b2_pa) before any joins The expression: b2_pa.bp_type = 0 is OPTIMIZED, covers 7901 rows or index keys, costs 225968 bytes, and will be applied to the billing_parties table (b2_pa) before any joins Join Ordering ------------- The driver table is the billing_parties table (b1) The billing_parties table (b1) is joined to the billing_parties table (b2_pa) with the INNER JOIN expression: b1.bp_linkto_pa = b2_pa.bp_linkto_pa Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order Optimized Join Ordering ----------------------- The driver table is the billing_parties table (b2_pa) The billing_parties table (b2_pa) is joined to the billing_parties table (b1) with the INNER JOIN expression: b2_pa.bp_linkto_pa = b1.bp_linkto_pa Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: b2_pa.bp_linkto_pa = b1.bp_linkto_pa is OPTIMIZED ================================================================================ >>>>> 38 rows affected in 1.732 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 6) ================================================================================ CREATE INDEX i_bp_filter ON MEMORY\bp_filter (bp_internal_id) ================================================================================ >>>>> 0 rows affected in 0 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 6) ================================================================================ SELECT b1.bp_internal_id , b1.bp_linkto_pa , b1.bp_type INTO MEMORY\pa_filter FROM billing_parties b1 INNER JOIN billing_parties b2_pa ON b1.bp_linkto_pa = b2_pa.bp_linkto_pa WHERE (b2_pa.bp_level = 0) AND ((b1.bp_type = 0) OR (b1.bp_type = 1) OR ((b1.bp_type = 2) AND (b1.bp_level in (2)))) AND (b2_pa.bp_linkto_th IN (84)) AND (b2_pa.bp_type = 0) GROUP BY b1.bp_linkto_pa Tables Involved --------------- billing_parties (b1) table opened shared, has 18794 rows billing_parties (b2_pa) table opened shared, has 18794 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more grouped rows Result set will be grouped by the following column(s) using a temporary index: bp_linkto_pa Result set will be ordered by the temporary index created for the grouping WHERE Clause Execution ---------------------- The expression: b2_pa.bp_level = 0 is OPTIMIZED, covers 11940 rows or index keys, costs 108654 bytes, and will be applied to the billing_parties table (b2_pa) before any joins The expression: b1.bp_type = 0 OR b1.bp_type = 1 OR b1.bp_type = 2 AND b1.bp_level in (2) is PARTIALLY-OPTIMIZED, covers 9755 rows or index keys, costs 278992 bytes, and will be applied to the billing_parties table (b1) before any joins The expression: b2_pa.bp_linkto_th IN (84) is OPTIMIZED, covers 96 rows or index keys, costs 6614 bytes, and will be applied to the billing_parties table (b2_pa) before any joins The expression: b2_pa.bp_type = 0 is OPTIMIZED, covers 7901 rows or index keys, costs 225968 bytes, and will be applied to the billing_parties table (b2_pa) before any joins Join Ordering ------------- The driver table is the billing_parties table (b1) The billing_parties table (b1) is joined to the billing_parties table (b2_pa) with the INNER JOIN expression: b1.bp_linkto_pa = b2_pa.bp_linkto_pa Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order Optimized Join Ordering ----------------------- The driver table is the billing_parties table (b2_pa) The billing_parties table (b2_pa) is joined to the billing_parties table (b1) with the INNER JOIN expression: b2_pa.bp_linkto_pa = b1.bp_linkto_pa Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: b2_pa.bp_linkto_pa = b1.bp_linkto_pa is OPTIMIZED ================================================================================ >>>>> 19 rows affected in 0.078 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 6) ================================================================================ CREATE INDEX i_pa_filter ON MEMORY\pa_filter (bp_linkto_pa) ================================================================================ >>>>> 0 rows affected in 0.016 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 6) ================================================================================ SELECT trans_internal_id , trans_linkto_bp , trans_linkto_pa INTO MEMORY\trans_filter FROM transactions WHERE (trans_type <> 0) AND (trans_type <> 2) AND (trans_type <> 3) AND (trans_type <> 4) AND (trans_date >= '2008-01-01') AND (trans_date < '2008-05-01') Tables Involved --------------- transactions (transactions) table opened shared, has 185019 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows WHERE Clause Execution ---------------------- The expression: trans_type <> 0 AND trans_type <> 2 AND trans_type <> 3 AND trans_type <> 4 AND trans_date >= '2008-01-01' AND trans_date < '2008-05-01' has been rewritten and is PARTIALLY-OPTIMIZED, covers 0 rows or index keys, costs 0 bytes, and will be applied to the transactions table (transactions) before any joins ================================================================================ >>>>> 11152 rows affected in 9.392 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 6) ================================================================================ CREATE INDEX i_trans_filter ON MEMORY\trans_filter (trans_internal_id) ================================================================================ >>>>> 0 rows affected in 0.047 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 6) ================================================================================ SELECT bp.bp_last_name + ', ' + bp.bp_first_name + IF (LENGTH(bp.bp_middle_initial) > 0 THEN ' ' + bp.bp_middle_initial ELSE '') AS bp_name , trans_internal_id , trans_type , pa.bp_last_name + ', ' + pa.bp_first_name + IF (LENGTH(pa.bp_middle_initial) > 0 THEN ' ' + pa.bp_middle_initial ELSE '') AS pa_name , trans_date , trans_writeoff , trans_sess_charge , trans_amount , trans_amount - trans_sess_charge + trans_writeoff AS trans_tax , trans_expected_amount , trans_pay_method , trans_pay_check_num , sess_diag_1 , sess_diag_2 , sess_diag_3 , sess_diag_4 , sess_diag_codeptr1 , sess_diag_codeptr2 , sess_diag_codeptr3 , sess_diag_codeptr4 , sess_units , ta_amount , IF (trans_type = 0 THEN ser_name ELSE IF (trans_type = 1 THEN 'Payment' ELSE IF (trans_type = 2 THEN 'Adjustment' ELSE IF (trans_type = 3 THEN 'Startup Balance' ELSE IF (trans_type = 4 THEN 'Interest' ELSE 'Unknown'))))) AS trans_desc , ta_linkto_trans , trans_linkto_th , trans_linkto_pr , trans_linkto_acc , pa.bp_internal_id AS pa_bp_internal_id , pa.bp_linkto_pa , pa.bp_last_name AS pa_last_name , pa.bp_first_name AS pa_first_name , pa.bp_middle_initial AS pa_middle_initial , bp.bp_internal_id , bp.bp_type , bp.bp_level , IFNULL (th_first_name THEN ' Transactions with no Therapist selected' ELSE th_last_name + ', ' + th_first_name + IF (LENGTH(th_middle_initial) > 0 THEN ' ' + th_middle_initial ELSE '') + IF (LENGTH(th_degree) > 0 THEN ' ' + th_degree ELSE '')) AS group_name , trans_linkto_th AS group_id FROM transactions INNER JOIN MEMORY\trans_filter ON trans_internal_id = trans_filter.trans_internal_id INNER JOIN billing_parties pa ON trans_linkto_pa = pa.bp_linkto_pa INNER JOIN MEMORY\pa_filter ON pa.bp_linkto_pa = pa_filter.bp_linkto_pa LEFT OUTER JOIN session ON trans_internal_id = sess_linkto_trans LEFT OUTER JOIN service_library ON trans_linkto_ser = ser_internal_id LEFT OUTER JOIN transaction_amount ON trans_internal_id = ta_linkto_trans INNER JOIN billing_parties bp ON ta_linkto_bp = bp.bp_internal_id INNER JOIN MEMORY\bp_filter ON ta_linkto_bp = bp_filter.bp_internal_id LEFT OUTER JOIN therapist_facesheet ON trans_linkto_th = th_internal_id WHERE (pa.bp_type = 0) AND (trans_date >= '2008-01-01') AND (trans_date < '2008-05-01') AND ((trans_type = 0 OR (NOT ta_linkto_trans IS NULL))) ORDER BY group_name , group_id , trans_date , pa.bp_last_name , pa.bp_first_name , pa.bp_middle_initial , pa.bp_internal_id , trans_internal_id , bp.bp_type , bp.bp_level NOCASE Tables Involved --------------- transactions (transactions) table opened shared, has 185019 rows trans_filter (trans_filter) table opened shared, has 11152 rows billing_parties (pa) table opened shared, has 18794 rows pa_filter (pa_filter) table opened shared, has 19 rows session (session) table opened shared, has 107435 rows service_library (service_library) table opened shared, has 115 rows transaction_amount (transaction_amount) table opened shared, has 244330 rows billing_parties (bp) table opened shared, has 18794 rows bp_filter (bp_filter) table opened shared, has 38 rows therapist_facesheet (therapist_facesheet) table opened shared, has 133 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Result set will be ordered by the following column(s) using a case-insensitive temporary index: group_name ASC group_id ASC trans_date ASC pa_last_name ASC pa_first_name ASC pa_middle_initial ASC pa_bp_internal_id ASC trans_internal_id ASC bp_type ASC bp_level ASC WHERE Clause Execution ---------------------- The expression: pa.bp_type = 0 is OPTIMIZED, covers 7901 rows or index keys, costs 225968 bytes, and will be applied to the billing_parties table (pa) before any joins The expression: trans_date >= '2008-01-01' is OPTIMIZED, covers 52759 rows or index keys, costs 823040 bytes, and will be applied to the transactions table (transactions) before any joins The expression: trans_date < '2008-05-01' is OPTIMIZED, covers 161604 rows or index keys, costs 2521022 bytes, and will be applied to the transactions table (transactions) before any joins Join Ordering ------------- The driver table is the transactions table (transactions) The transactions table (transactions) is joined to the trans_filter table (trans_filter) with the INNER JOIN expression: trans_internal_id = trans_filter.trans_internal_id The transactions table (transactions) is joined to the billing_parties table (pa) with the INNER JOIN expression: trans_linkto_pa = pa.bp_linkto_pa The transactions table (transactions) is joined to the session table (session) with the LEFT OUTER JOIN expression: trans_internal_id = sess_linkto_trans The transactions table (transactions) is joined to the service_library table (service_library) with the LEFT OUTER JOIN expression: trans_linkto_ser = ser_internal_id The transactions table (transactions) is joined to the transaction_amount table (transaction_amount) with the LEFT OUTER JOIN expression: trans_internal_id = ta_linkto_trans The transactions table (transactions) is joined to the therapist_facesheet table (therapist_facesheet) with the LEFT OUTER JOIN expression: trans_linkto_th = th_internal_id The billing_parties table (pa) is joined to the pa_filter table (pa_filter) with the INNER JOIN expression: pa.bp_linkto_pa = pa_filter.bp_linkto_pa The transaction_amount table (transaction_amount) is joined to the billing_parties table (bp) with the INNER JOIN expression: ta_linkto_bp = bp.bp_internal_id The transaction_amount table (transaction_amount) is joined to the bp_filter table (bp_filter) with the INNER JOIN expression: ta_linkto_bp = bp_filter.bp_internal_id Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order Optimized Join Ordering ----------------------- The driver table is the pa_filter table (pa_filter) The pa_filter table (pa_filter) is joined to the billing_parties table (pa) with the INNER JOIN expression: pa_filter.bp_linkto_pa = pa.bp_linkto_pa The billing_parties table (pa) is joined to the transactions table (transactions) with the INNER JOIN expression: pa.bp_linkto_pa = trans_linkto_pa The transactions table (transactions) is joined to the trans_filter table (trans_filter) with the INNER JOIN expression: trans_internal_id = trans_filter.trans_internal_id The transactions table (transactions) is joined to the session table (session) with the LEFT OUTER JOIN expression: trans_internal_id = sess_linkto_trans The transactions table (transactions) is joined to the service_library table (service_library) with the LEFT OUTER JOIN expression: trans_linkto_ser = ser_internal_id The transactions table (transactions) is joined to the transaction_amount table (transaction_amount) with the LEFT OUTER JOIN expression: trans_internal_id = ta_linkto_trans The transactions table (transactions) is joined to the therapist_facesheet table (therapist_facesheet) with the LEFT OUTER JOIN expression: trans_linkto_th = th_internal_id The transaction_amount table (transaction_amount) is joined to the billing_parties table (bp) with the INNER JOIN expression: ta_linkto_bp = bp.bp_internal_id The transaction_amount table (transaction_amount) is joined to the bp_filter table (bp_filter) with the INNER JOIN expression: ta_linkto_bp = bp_filter.bp_internal_id Scan Expression Execution ------------------------- The expression: trans_type = 0 OR NOT ta_linkto_trans IS NULL is UN-OPTIMIZED and will be applied to each candidate row in the result set as the result set is generated Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: pa_filter.bp_linkto_pa = pa.bp_linkto_pa is OPTIMIZED The expression: pa.bp_linkto_pa = trans_linkto_pa is OPTIMIZED The expression: trans_internal_id = trans_filter.trans_internal_id is OPTIMIZED The expression: trans_internal_id = sess_linkto_trans is OPTIMIZED The expression: trans_linkto_ser = ser_internal_id is OPTIMIZED The expression: trans_internal_id = ta_linkto_trans is OPTIMIZED The expression: ta_linkto_bp = bp.bp_internal_id is OPTIMIZED The expression: ta_linkto_bp = bp_filter.bp_internal_id is OPTIMIZED The expression: trans_linkto_th = th_internal_id is OPTIMIZED ================================================================================ >>>>> 1 rows affected in 4.914 seconds ================================================================================ |
Wed, Oct 1 2008 12:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Michael,
<< Here is the plan. Almost near the bottom is one UN-OPTIMIZED scan selection on trans_type = 0 OR NOT ta_linkto_trans IS NULL There is an index on trans_type and ta_linkto_trans is from a table that is connected through an outer join. Would this be the cause and if so, how can that statement be optimized? >> That statement won't be an issue since it won't cause a read lock to be held on the source table. The one statement that strikes me as odd, however, is this one: trans_type <> 0 AND trans_type <> 2 AND trans_type <> 3 AND trans_type <> 4 AND trans_date >= '2008-01-01' AND trans_date < '2008-05-01' 11152 rows affected in 9.392 seconds It indicates that the I/O cost is 0 rows, yet 11152 rows are generated in the result set. Could you possibly email me the transactions table ? I'm going to have to evaluate this one to see: 1) Why the I/O cost projection is wrong, and 2) Why it is taking 9 seconds to generate 11,000+ rows. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |