Recent

Author Topic: save result of a TSQLQuery as a database table  (Read 2262 times)

tatamata

  • Hero Member
  • *****
  • Posts: 787
    • ZMSQL - SQL enhanced in-memory database
save result of a TSQLQuery as a database table
« on: November 13, 2017, 09:25:37 am »
In Postgresql 10 one can gain huge performance improvements by using parallelism. Parallel query execution is however possible only for SELECT queries, while not possible for any DDL statements including SELECT...INTO or CREATE TABLE AS SELECT...statements.
I have a CREATE TABLE AS SELECT.. type of query, which cannot utilize parallelism. In order to utilize Postgresql query parallelism, I intend to turn the query into normal SELECT query and then save resultset back to database, as a table.

How can I save TSQLQuery resultset back to database as a table?

tudi_x

  • Hero Member
  • *****
  • Posts: 532
Re: save result of a TSQLQuery as a database table
« Reply #1 on: November 13, 2017, 09:43:51 am »
aren't you loosing with moving the data back and forth through 5432 what you gain through parallelism?
Lazarus 2.0.2 64b on Debian LXDE 10

tatamata

  • Hero Member
  • *****
  • Posts: 787
    • ZMSQL - SQL enhanced in-memory database
Re: save result of a TSQLQuery as a database table
« Reply #2 on: November 13, 2017, 09:45:56 am »
aren't you loosing with moving the data back and forth through 5432 what you gain through parallelism?
I doubt it, because in this particular case it is an extremely long-running query, actually a cartesian product, so...

tudi_x

  • Hero Member
  • *****
  • Posts: 532
Re: save result of a TSQLQuery as a database table
« Reply #3 on: November 13, 2017, 10:14:01 am »
what is the drawback of having the operation in a stored procedure?
i think you could use a cursor and insert the data in a create as select with zero rows table that would be instant.
Lazarus 2.0.2 64b on Debian LXDE 10

tatamata

  • Hero Member
  • *****
  • Posts: 787
    • ZMSQL - SQL enhanced in-memory database
Re: save result of a TSQLQuery as a database table
« Reply #4 on: November 13, 2017, 10:20:04 am »
what is the drawback of having the operation in a stored procedure?
i think you could use a cursor and insert the data in a create as select with zero rows table that would be instant.
Could you explain your idea more thoroughly, I'm not sure I got it?

tudi_x

  • Hero Member
  • *****
  • Posts: 532
Re: save result of a TSQLQuery as a database table
« Reply #5 on: November 13, 2017, 10:28:13 am »
you create a cursor into which you fetch the result of the select x,y from T1
you create the target T2 as create table T2 as select x,y from T1 limit 0
the data from cursor you insert in T2

besides everything remaining in database you have the same types as dataset types do not match DB types in 100% cases.
« Last Edit: November 13, 2017, 10:29:50 am by tudi_x »
Lazarus 2.0.2 64b on Debian LXDE 10

 

TinyPortal © 2005-2018