Tsqlquery.edit, Update commands or SQL statements?
lazarus / 16-Databases

----------------------------------------------------------------------
Ign_17 · 2015-11-24 18:33

Hi everyone,

I would like to read some opinions about what is best (faster,
efficient, etc) in order to work with the Tsqlquery component: when we
use SQL statements via the SQL property, or using directly the Edit,
Append or Delete functions.


For example, if i would like to first locate, and then modify (if the
record exists) or insert (if not exists)  a specific record on a
table, I have two choices (this assuming the "UPDATE OR INSERT" SQL
command is not supported in my database):

- A SELECT sentence written in TsqlQuery.SQL.Text.
    if SqlQuery.Empty = TRUE then an INSERT sentence in
TsqlQuery.SQL.Text
    else an UPDATE sentence in TsqlQuery.Text

- A SELECT sentence in TsqlQuery.SQL.Text.
   if SqlQuery.Empty = TRUE then TsqlQuery.Append and TsqlQuery.Post
commands
   else TsqlQuery.Edit and TsqlQuery .Post commands.

What of this two choices would be the best? And, what is the
difference on using Edit, Update or Delete commands, and SQL sentences
in the TsqlQuery.SQL property?

I hope I explained my question well.

Thanks in advance for any answer.

----------------------------------------------------------------------
LacaK · 2015-11-24 19:00

Try read: http://wiki.freepascal.org/SqlDBHowto#How_does_SqlDB_send_th
e_changes_to_the_database_server.3F

http://wiki.freepascal.org/Working_With_TSQLQuery#Cached_Updates

----------------------------------------------------------------------
Ign_17 · 2015-11-24 19:38

Thank you very much for the links.

I have read them, and I know how to work with TsqlQery commands and
the Providersflags properties... but my doubt persists. What is best,
SQL sentences (INSERT, UPDATE, DELETE) or SqlQuery commands (Edit,
Append, Delete) ? Is one of them faster or efficient then the other?
Are the Edit, Append and Delete commands internally traduced to SQL
sentences so would be faster working with SQL sentences directly via
TsqlQuery.SQL.TExt instead of TsqlQuery commands?

Thanks again.

----------------------------------------------------------------------
taazz · 2015-11-24 20:23

Quote from: Ign_71 on November 24, 2015, 08:38:39 pmThank you very
much for the links.

I have read them, and I know how to work with TsqlQery commands and
the Providersflags properties... but my doubt persists. What is best,
SQL sentences (INSERT, UPDATE, DELETE) or SqlQuery commands (Edit,
Append, Delete) ? Is one of them faster or efficient then the other?
Are the Edit, Append and Delete commands internally traduced to SQL
sentences so would be faster working with SQL sentences directly via
TsqlQuery.SQL.TExt instead of TsqlQuery commands?

Thanks again.
is it a long or short leaved query?Is the query destroyed after the
edit operation or it is kept alive (to show data on a grid for
example)? In general using the query commands you make sure that the
query data are not out of sync with the user's changed data. There are
other problems to be considered here outside the pure speed of
updating the database, but to answer your question all query commands
are in the end converted to sql commands and executed on the server to
update the data.

----------------------------------------------------------------------
Ign_17 · 2015-11-24 22:09

Quoteis it a long or short leaved query?Is the query destroyed after
the edit operation or it is kept alive (to show data on a grid for
example)? In general using the query commands you make sure that the
query data are not out of sync with the user's changed data. There are
other problems to be considered here outside the pure speed of
updating the database, but to answer your question all query commands
are in the end converted to sql commands and executed on the server to
update the data.
Excellent!!  Thank you very much for your answer.

REgards.