Very Computer

My client software is written in Delphi.

My Delphi program accepts user from which the dataset is selected.

Which is a better approach:

Approach 1:
Have Delphi contain logic to build the SQL select query, then pass the SQL
to the server for execution.

Approach 2:
Have Delphi call a SQL stored procedure, passing parameters that specify how
to build the query, then let the SQL sp build the query dynamically and
execute via exec.

I like the idea of Approach 2, but not if there is a big performance hit.

Keith,

In the scenario presented, I'd go with the second approach, AND I'd use
sp_ExecuteSQL instead of EXEC. You have an increased probability of having
the query plan cached by using sp_ExecuteSQL.

You should have a performance improvement rather than a performance
degradation by following the second approach, but more importantly, IMHO,
the huge advantage of the second approach over the first is if you need to
change the SQL code in your application, you can change it in a single place
rather than having to hunt down every place where the Delphi application is
executing and trying to update the Delphi .exe's simultaneously.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> My client software is written in Delphi.

> My Delphi program accepts user from which the dataset is selected.

> Which is a better approach:

> Approach 1:
> Have Delphi contain logic to build the SQL select query, then pass the SQL
> to the server for execution.

> Approach 2:
> Have Delphi call a SQL stored procedure, passing parameters that specify
how
> to build the query, then let the SQL sp build the query dynamically and
> execute via exec.

> I like the idea of Approach 2, but not if there is a big performance hit.

I will try that later today. I was hoping to avoid any temp tables.

What I have is a list of customers in one table, and a list of phones in
another. Each cust has 0 or more phones. I'm building a list of customers
with their main phone number (where Seq is min for that customer). This list
is built whenever I need a lookup list for customers, so, I'm worried about
execution speed.

Quote:> My client software is written in Delphi.

> My Delphi program accepts user from which the dataset is selected.

> Which is a better approach:

> Approach 1:
> Have Delphi contain logic to build the SQL select query, then pass the SQL
> to the server for execution.

> Approach 2:
> Have Delphi call a SQL stored procedure, passing parameters that specify
how
> to build the query, then let the SQL sp build the query dynamically and
> execute via exec.

> I like the idea of Approach 2, but not if there is a big performance hit.