View or Stored procedure

**lilelvis2000** · 15 October 2010, 08:32

The choice really depends on how complex these views and whether it is faster to use temporary tables or other processing to produce the result you want.

From your description it looks like just plain views sounds the way to go.

As for GUI - if you have Access - why not use that as the GUI.

**ThomasSoerensen** · 15 October 2010, 08:44

Originally posted by ThomasSoerensen View Post

Hi guys

I have no formal database education and this is why I have trouble selecting one way of doing things over another.

what is the general consensus on this?

I have a database that is used in the way that every time I use it I load new data into a list of "base"tables. Then I run the same queries against the data and use the result to write a report.
Before I use the database for the next report I empty all the base tables of old data, then load the new data into the base tables and then I run all the queries.

I am the only user in the database.

The RDBMS is MS SQL 2008 with SQL management studio as editor. Currently I have no GUI. But in time I would like to write a GUI so the use of the database is more sleek.

In this scenario, would you recommend that I have my queries defined in views that are run or that the queries are defined in stored procedures?

I look forward to a good learning experience about the purpose of views and stored procedures and pros and cons for each and then a recommendation based on my scenario.

cheers
Thomas

So you would choose based on execution speed?

Are there not any other big pro's or con's to either?

As an example I just ran the same query as a view and as stored procedure.

To be fair, the View just outputs the result to the result viewer in the Management studio and the stored procedure inserts the result into a table, so the stored procedure does an additional task.
The result was that the view completed in 8 minutes and 43 seconds and the stored procedure completed in 6 minutes and 54 seconds. So a 1 minute and 49 seconds improvement in execution time. That is quite a lot.

Hm.

I just read that the query plan is cached in a stored procedure so I thought the result from running it only once is not representative.
Here are the result of running the same query again.
As a view 10:41
As a stored procedure 7:23
Difference 3:14
But both are slower now. Hm.

**Spacecadet** · 15 October 2010, 08:56

Originally posted by ThomasSoerensen View Post

I look forward to a good learning experience about the purpose of views and stored procedures

You missed out table valued functions

Given that
a) you have no formal training
b) you're the only DB user

why don't you try all methods? Thanks to cut and paste it won't take long and then you can see which one best fits your situation!

**Spacecadet** · 15 October 2010, 08:58

Originally posted by ThomasSoerensen View Post

The result was that the view completed in 8 minutes and 43 seconds and the stored procedure completed in 6 minutes and 54 seconds. So a 1 minute and 49 seconds improvement in execution time. That is quite a lot.

Hm.

How is your insert statement constructed?
How much data is there?
Are you dropping indexes before hand (if there are any)?

Unless you're will to share your table structure, SQL and give up some sample data it's difficult to give you a sensible answer

**ThomasSoerensen** · 15 October 2010, 09:03

Originally posted by Spacecadet View Post

You missed out table valued functions

Given that
a) you have no formal training
b) you're the only DB user

why don't you try all methods? Thanks to cut and paste it won't take long and then you can see which one best fits your situation!

I had no idea they existed. Due to not being formally trained in the RDBMS I guess.

Again, is the major difference between these ways of doing things execution speed. Or is there a big benefit to one of them that I will be grateful for later if I choose the "correct" way of doing things.

**Spacecadet** · 15 October 2010, 09:16

Originally posted by ThomasSoerensen View Post

I had no idea they existed. Due to not being formally trained in the RDBMS I guess.

Again, is the major difference between these ways of doing things execution speed. Or is there a big benefit to one of them that I will be grateful for later if I choose the "correct" way of doing things.

Stored procedure - gives you total T-SQL access to the database.
For your purposes, it's going to be the best option as it will select your data from source then dump it into your reporting tables and even select it back out for you if you want. Plus you can add a parameter which instructs the stored procedure if you want to refresh the base tables or not.

If you want a handy front end for getting the data and creating a report then use Excel, from the VBA you can run your stored procedures and take the data, format it and do whatever else you want with it.

**MarillionFan** · 15 October 2010, 09:18

How fast are we talking by the way?

If you're existing report is pretty much instaneous, then I wouldn't bother spending any more time on the 'best' way.

**ThomasSoerensen** · 15 October 2010, 09:28

Originally posted by Spacecadet View Post

How is your insert statement constructed?
How much data is there?
Are you dropping indexes before hand (if there are any)?

Unless you're will to share your table structure, SQL and give up some sample data it's difficult to give you a sensible answer

The amount of data is in one generated base table "t_roles_and_profiles_all" (a union of several base tables) there are 767.224 records and in the other generated base table "t_users_with_roles_and_profiles_all" (a union of a few other base tables) there are 10.238 records.
There will often be larger data sets - up to ten times as large is a reasonable estimate.

These two generated base tables are where all the queries are looking when querying.

I do not drop indexes when refreshing the data. I have created many indexes and I assume that when I delete and repopulate the data in the basetables then the indexes are updated accordingly.

Here is the example I just ran as a stored procedure.

USE [CSS_20091002]
GO
/****** Object: StoredProcedure [dbo].[p_oa_test_F_BKPF_BUK] Script Date: 10/15/2010 10:32:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE [dbo].[p_ua_test_SCC1_detail]
-- Add the parameters for the stored procedure here

as
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

insert into t_export_ua_004_SCC1_detail_result
SELECT TOP (100) PERCENT 'SCC1' AS [Check], dbo.t_users_with_roles_and_profiles_all.SID, dbo.t_users_with_roles_and_profiles_all.ScanID,
dbo.t_users_with_roles_and_profiles_all.BNAME, dbo.t_users_with_roles_and_profiles_all.USTYP, dbo.t_users_with_roles_and_profiles_all.GLTGV,
dbo.t_users_with_roles_and_profiles_all.GLTGB, dbo.t_users_with_roles_and_profiles_all.UFLAG, dbo.v_oa_S_TCODE_SCC1.RowSource AS [S_TCODE-RowSource],
dbo.v_oa_S_TCODE_SCC1.CompositeRole AS [S_TCODE-Composite-Role], dbo.v_oa_S_TCODE_SCC1.Role AS [S_TCODE-role],
dbo.v_oa_S_TCODE_SCC1.PROFILE AS [S_TCODE-Profile], dbo.v_oa_S_TCODE_SCC1.OBJECT AS [S_TCODE-Object],
dbo.v_oa_S_TCODE_SCC1.AUTH AS [S_TCODE-Auth], dbo.v_oa_S_TCODE_SCC1.FIELD AS [S_TCODE-field], dbo.v_oa_S_TCODE_SCC1.LOW AS [S_TCODE-valueLow],
dbo.v_oa_S_TCODE_SCC1.HIGH AS [S_TCODE-ValueHigh], dbo.v_oa_S_CLNT_IMP_ACTVT_21.RowSource AS [S_CLNT_IMP-RowSource],
dbo.v_oa_S_CLNT_IMP_ACTVT_21.CompositeRole AS [S_CLNT_IMP-Composite-Role], dbo.v_oa_S_CLNT_IMP_ACTVT_21.Role AS [S_CLNT_IMP-Role],
dbo.v_oa_S_CLNT_IMP_ACTVT_21.PROFILE AS [S_CLNT_IMP-Profile], dbo.v_oa_S_CLNT_IMP_ACTVT_21.OBJECT AS [S_CLNT_IMP-Object],
dbo.v_oa_S_CLNT_IMP_ACTVT_21.AUTH AS [S_CLNT_IMP-Auth], dbo.v_oa_S_CLNT_IMP_ACTVT_21.FIELD AS [S_CLNT_IMP-Field],
dbo.v_oa_S_CLNT_IMP_ACTVT_21.LOW AS [S_CLNT_IMP-ValueFrom], dbo.v_oa_S_CLNT_IMP_ACTVT_21.HIGH AS [S_CLNT_IMP-ValueTo],
dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X.RowSource AS [S_TABU_CLI-RowSource],
dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X.CompositeRole AS [S_TABU_CLI-Composite-Role], dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X.Role AS [S_TABU_CLI-Role],
dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X.PROFILE AS [S_TABU_CLI-Profile], dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X.OBJECT AS [S_TABU_CLI-Object],
dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X.AUTH AS [S_TABU_CLI-Auth], dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X.FIELD AS [S_TABU_CLI-Field],
dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X.LOW AS [S_TABU_CLI-ValueFrom], dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X.HIGH AS [S_TABU_CLI-ValueTo],
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.RowSourc e AS [S_TABU_DIS-RowSource],
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.Composit eRole AS [S_TABU_DIS-Composite-Role],
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.Role AS [S_TABU_DIS-Role],
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.PROFILE AS [S_TABU_DIS-Profile],
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.OBJECT AS [S_TABU_DIS-object],
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.AUTH AS [S_TABU_DIS-Auth], dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.FIELD AS [S_TABU_DIS-Field],
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.LOW AS [S_TABU_DIS-ValueFrom],
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.HIGH AS [S_TABU_DIS-ValueTo],
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.FIELD2 AS [S_TABU_DIS-Field2],
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.VON AS [S_TABU_DIS-Field2ValueFrom],
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.BIS AS [S_TABU_DIS-Field2ValueTo]
FROM dbo.t_users_with_roles_and_profiles_all INNER JOIN
dbo.t_users_with_roles_and_profiles_all AS t_users_with_roles_and_profiles_all_1 ON
dbo.t_users_with_roles_and_profiles_all.BNAME = t_users_with_roles_and_profiles_all_1.BNAME AND
dbo.t_users_with_roles_and_profiles_all.ScanID = t_users_with_roles_and_profiles_all_1.ScanID INNER JOIN
dbo.t_users_with_roles_and_profiles_all AS t_users_with_roles_and_profiles_all_2 ON
dbo.t_users_with_roles_and_profiles_all.BNAME = t_users_with_roles_and_profiles_all_2.BNAME AND
dbo.t_users_with_roles_and_profiles_all.ScanID = t_users_with_roles_and_profiles_all_2.ScanID INNER JOIN
dbo.t_users_with_roles_and_profiles_all AS t_users_with_roles_and_profiles_all_3 ON
t_users_with_roles_and_profiles_all_1.BNAME = t_users_with_roles_and_profiles_all_3.BNAME AND
t_users_with_roles_and_profiles_all_1.ScanID = t_users_with_roles_and_profiles_all_3.ScanID INNER JOIN
dbo.v_oa_S_TCODE_SCC1 ON dbo.t_users_with_roles_and_profiles_all.CompositeR ole = dbo.v_oa_S_TCODE_SCC1.CompositeRole AND
dbo.t_users_with_roles_and_profiles_all.ScanID = dbo.v_oa_S_TCODE_SCC1.ScanID INNER JOIN
dbo.v_oa_S_CLNT_IMP_ACTVT_21 ON t_users_with_roles_and_profiles_all_1.CompositeRol e = dbo.v_oa_S_CLNT_IMP_ACTVT_21.CompositeRole AND
t_users_with_roles_and_profiles_all_1.ScanID = dbo.v_oa_S_CLNT_IMP_ACTVT_21.ScanID INNER JOIN
dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X ON t_users_with_roles_and_profiles_all_2.CompositeRol e = dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X.CompositeRole AND
t_users_with_roles_and_profiles_all_2.ScanID = dbo.v_oa_S_TABU_CLI_CLIIDMAINT_X.ScanID INNER JOIN
dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS ON
t_users_with_roles_and_profiles_all_3.CompositeRol e = dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.Composit eRole AND
t_users_with_roles_and_profiles_all_3.ScanID = dbo.v_oa_S_TABU_DIS_ACTVT_02_DICBERCLS_SS.ScanID
ORDER BY dbo.t_users_with_roles_and_profiles_all.BNAME

-- where ScanID=@ScanID

-- Insert statements for procedure here

END

**ThomasSoerensen** · 15 October 2010, 09:32

Originally posted by Spacecadet View Post

Stored procedure - gives you total T-SQL access to the database.
For your purposes, it's going to be the best option as it will select your data from source then dump it into your reporting tables and even select it back out for you if you want. Plus you can add a parameter which instructs the stored procedure if you want to refresh the base tables or not.

If you want a handy front end for getting the data and creating a report then use Excel, from the VBA you can run your stored procedures and take the data, format it and do whatever else you want with it.

Thanks.

could you explain in a few words what you mean by "even select it back out for you if you want" after the data is dumped into the result table. What is this and why could one need it?

View or Stored procedure