* * *

Author Topic: Running several SELECT commands in one query  (Read 1026 times)

Vodnik

  • New member
  • *
  • Posts: 40
Running several SELECT commands in one query
« on: May 04, 2018, 01:40:45 pm »
I was using the code:
Code: Pascal  [Select]
  1.     DBForm.DBquery2.Close;
  2.     DBForm.DBquery2.SQL.text:='select min(callstart + sitetzoffset units minute), max(callstart + sitetzoffset units minute), count(*), count(distinct callid) from callrecord;';
  3.     DBForm.DBquery2.Open;
  4.     CallRecordStartDateTime:=DBForm.DBquery2.Fields[0].AsDateTime;
  5.     CallRecordEndDateTime:=DBForm.DBquery2.Fields[1].AsDateTime;
  6.     Total:=DBForm.DBquery2.Fields[2].AsLongInt;
  7.     Unique:=DBForm.DBquery2.Fields[3].AsLongInt;
  8.     DBForm.DBquery2.Close;
  9.  
to obtain several values from a table. But soon I have discovered, that combined SELECT
Code: Pascal  [Select]
  1. select min(callstart + sitetzoffset units minute), max(callstart + sitetzoffset units minute), count(all callid), count(distinct callid) from callrecord;
runs approx. 3 times longer than 4 separate ones. Do not know why this is so (Informix via ODBC), but I changed the code:
Code: Pascal  [Select]
  1.       DBForm.DBquery2.Close;
  2.      DBForm.DBquery2.Clear;
  3.     DBForm.DBquery2.SQL.Add('select min(callstart + sitetzoffset units minute) from callrecord;');
  4.     DBForm.DBquery2.SQL.Add('select max(callstart + sitetzoffset units minute) from callrecord;');
  5.     DBForm.DBquery2.SQL.Add('select count(*) from callrecord;');
  6.     DBForm.DBquery2.SQL.Add('select count(distinct callid) from callrecord;');
  7.     DBForm.DBquery2.Open;
  8.     CallRecordStartDateTime:=DBForm.DBquery2.Fields[0].AsDateTime;
  9.     CallRecordEndDateTime:=DBForm.DBquery2.Fields[1].AsDateTime;
  10.     Total:=DBForm.DBquery2.Fields[2].AsLongInt;
  11.     Unique:=DBForm.DBquery2.Fields[3].AsLongInt;
  12.     DBForm.DBquery2.Close;
  13.  
but this produce SQL error:
Could not prepare statement. ODBC error details: LastReturnCode: SQL_ERROR;Could not get error message: An invalid parameter was passed to SQLGetDiagRec/Field
Then how is it possible to run several SELECT commands in one query?

rvk

  • Hero Member
  • *****
  • Posts: 3461
Re: Running several SELECT commands in one query
« Reply #1 on: May 04, 2018, 01:43:47 pm »
You can't do multiple selects with TSQLQuery.

But you can adjust your query to return all the fields.

This might work (depending on database):
Code: SQL  [Select]
  1. SELECT
  2.   MIN(callstart + sitetzoffset units MINUTE),
  3.   MAX(callstart + sitetzoffset units MINUTE),
  4.   COUNT(*),
  5.   COUNT(DISTINCT callid)
  6. FROM callrecord

Vodnik

  • New member
  • *
  • Posts: 40
Re: Running several SELECT commands in one query
« Reply #2 on: May 04, 2018, 01:51:56 pm »
You can't do multiple selects with TSQLQuery.

But you can adjust your query to return all the fields.

This might work (depending on database):
Code: SQL  [Select]
  1. SELECT
  2.   MIN(callstart + sitetzoffset units MINUTE),
  3.   MAX(callstart + sitetzoffset units MINUTE),
  4.   COUNT(*),
  5.   COUNT(DISTINCT callid)
  6. FROM callrecord

 :(
RVK, you suggested just my first example

rvk

  • Hero Member
  • *****
  • Posts: 3461
Re: Running several SELECT commands in one query
« Reply #3 on: May 04, 2018, 02:06:42 pm »
RVK, you suggested just my first example
Then you need to run the queries separately directly after each other. It's not possible to do multiple selects in one TSQLQuery, period.

Or find out why your original query is slow.

Another alternative would be to use UNION to patch these queries together.
You could do that with one record of with 4.

Either (4 record, 1 field):
Code: SQL  [Select]
  1. SELECT MIN(callstart + sitetzoffset units MINUTE)
  2. FROM callrecord
  3. UNION ALL
  4. SELECT MAX(callstart + sitetzoffset units MINUTE)
  5. FROM callrecord
  6. UNION ALL
  7. SELECT COUNT(*)
  8. FROM callrecord
  9. UNION ALL
  10. SELECT COUNT(DISTINCT callid)
  11. FROM callrecord

Or (1 record, 4 fields):
Code: SQL  [Select]
  1. SELECT MIN(callstart + sitetzoffset units MINUTE),NULL,NULL,NULL
  2. FROM callrecord
  3. UNION ALL
  4. SELECT NULL,MAX(callstart + sitetzoffset units MINUTE),NULL,NULL
  5. FROM callrecord
  6. UNION ALL
  7. SELECT NULL,NULL,COUNT(*),NULL
  8. FROM callrecord
  9. UNION ALL
  10. SELECT NULL,NULL,NULL,COUNT(DISTINCT callid)
  11. FROM callrecord

But I would find out, by checking the indexes and plan, why your other query is slow.

Vodnik

  • New member
  • *
  • Posts: 40
Re: Running several SELECT commands in one query
« Reply #4 on: May 04, 2018, 10:20:29 pm »
Here is another example of single SQL query (from SQL forum):
Code: SQL  [Select]
  1. SELECT  
  2.    (SELECT COUNT(DISTINCT callid) FROM callrecord) distinct_callid_cnt,
  3.    (SELECT COUNT(*) FROM callrecord) count_all,
  4.    (SELECT MIN(callstart) FROM callrecord) min_callstart,
  5.    (SELECT MAX(callstart) FROM callrecord) max_callstart
  6. FROM TABLE(SET{1})
  7.  
It runs fast, as separate SELECTs do.
Thank you for idea about plan, I didn't know about it.
« Last Edit: May 04, 2018, 11:04:57 pm by Vodnik »

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus