April 21, 2005 at 2:17 am
Hi,
All my SQL developments are for internet based systems and I was wondering which of the following 4 methods of querying is most efficient.
1) Build the query in Vb (.Asp) and send it straight to the Db.
I.E
Sql = "SELECT dbo.Customers.CustomerID,"
Sql = Sql & " dbo.[Order Details].UnitPrice,"
Sql = Sql & " dbo.[Order Details].Quantity"
Sql = Sql & " FROM dbo.Customers INNER JOIN"
Sql = Sql & " dbo.Orders ON dbo.Customers.CustomerID = "
Sql = Sql & " dbo.Orders.CustomerID INNER JOIN"
Sql = Sql & " dbo.[Order Details] ON dbo.Orders.OrderID = "
Sql = Sql & " dbo.[Order Details].OrderID"
Sql = Sql & " WHERE (dbo.Customers.CustomerID = N'Alfki')"
Set RSet = Connection.Execute(Sql)
(The 'Alfki' value could then be a variable inserted at runtime)
2) Build the basic query as a View, then open the view with the required parameters from the Vb (.Asp) page.
Sql = "Select CustomerId, UnitPrice, Quantity"
Sql = Sql & " FROM View_MyView"
Sql = Sql & " WHERE CustomerId = 'Alfki'"
Set Rset = Connection.Execute(Sql)
3) Build the basic query as a view then open the view from a stored procedure which is called from the VB (.Asp) Page
Sql = "Exec SP_Open_MyView @Cid='Alfki'"
Set Rset = Connection.Execute(Sql)
4) Put everything in the Stored Procedure (IE, the View) so it doesn't have to call on an external view and open it in much the same way as number 3 above.
I get the impression that number 4 is probably the most efficient, but is it? Also, there are differences in permissions for each which probably has to be considered alongside efficiency.
Regards
Cp
April 21, 2005 at 12:01 pm
Prbly some experts over here might have a different opinion. You can have the basic select statement in a view and index the view and call the view from a stored proc with a parameter.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
April 21, 2005 at 12:57 pm
This query is way to simple to try to do something fancy with it... I'd just create a proc with the full select statement. Don't forget that the indexed view is in reality a third table where all the table is kept/maintained. Which can cause a lot of overhead.
April 21, 2005 at 1:16 pm
If the query can be performed repeatedly and different parameter values then without doubts, go for the stored procedure!
hth
* Noel
April 21, 2005 at 4:52 pm
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
April 21, 2005 at 6:31 pm
The overhead Remi is talking about is:
For each insert in Customer, Orders and Order Detail The index in the view has to be refreshed! therefore it may speed up the reading of such infomation but it does slows down the insert/updates on the above mentioned tables
* Noel
April 22, 2005 at 1:20 am
Hmm,
I can see my question was not as clear as it could have been. The example given was just that, an example. I was more interested in which of the 4 methods was the best to use.
Thanks for all the input though.
Regards
Conway
April 22, 2005 at 6:32 am
My and Noeld's anwser still won't change about this. Use a sp without the view and you'll be all set.
April 22, 2005 at 8:29 am
Using SP's and generating a query plan is more efficient with our web apps. This is particuarlly true when accessing the SP's with web services.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply