July 3, 2014 at 2:42 pm
This function is in a view - linked to MS Access that uses a query to pass one ID_Well at a time, to return a status.
This is working fine.
Would appreciate any advice on improvement or general comments as I am a rank beginner.
USE [MyDB]
GO
/****** Object: UserDefinedFunction [dbo].[R_35] Script Date: 07/03/2014 14:29:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- R_35A function and R_35_B - if Both return 0 then return False, else True
-- only 1 record and 1 field will be returned per function call
ALTER FUNCTION [dbo].[R_35](@ID_Wells int)
RETURNS int
AS
BEGIN;
DECLARE @Result as int;
set @Result = (SELECT dbo.R_35A(ID_Wells) AS R_35A
FROM dbo.Wells where ID_Wells = @ID_Wells);
if @Result =1
set @Result = -1-- -1 is True in Access
else
begin
set @Result = (SELECT dbo.R_35B(ID_Wells) AS R_35B
FROM dbo.Wells where ID_Wells = @ID_Wells);
if @Result =1
set @Result = -1-- -1 is True in Access
else
set @Result =0 -- 0 is False in Access
end
return @result
END;
The VBA version of this function runs about 20 times slower.
The VBA version brought seven views across the wire and evaluated them on the client.
Now, this is all done on the Server side.
July 3, 2014 at 3:30 pm
Can you post the code for R_35A and R_35B functions? This sounds like it will generate a terrible bottleneck. Could you post as well an example on how do you use the function in the view?
DDL, Sample data (posted as insert statements) and expected results will benefit the quality of the answers.
July 6, 2014 at 4:17 am
From this code alone there's no way to tell you how it's going to perform. In addition to the code requests, if you can capture the execution plans, that would help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 6, 2014 at 1:03 pm
I would also like to see the code for the view using this function.
July 7, 2014 at 9:27 am
Just getting back over extended 4th of July weekend.
Will post some examples soon. Thanks again for the valid concerns of efficiency.
In general, most SQL Server functions are used for large transactions, the idea of efficiency is not lost on me.
Will be open to suggestions.
Preliminary test show the existing function is about a hundred times faster than the prototype client-server function.
The prototype is in actual production. My effort is to convert VBA Rule functions code over to SQL function.
The use of these functions are for a Business Rule Engine.
The book review for the concept can be found at:
http://www.amazon.com/Build-Business-Rules-Engine-Functionality-ebook/dp/B001CD9X9I
Taking the concept, the Rule Engine was designed in MS Access.
The Discrete Rules includes about 75 Functions that basically return a True or False.
Key: one primary key - very small queries as part of function - return a single True or False.
The next layer of Status Rules utilize a matrix that chooses 1 to many Discrete Rules and compares the result to a expected matrix of True False.
The Status Rule returns a True or the integer number of the first Discrete Rule that failed. Rule numbers are table driven.
This is not a large transaction DB. The typical use is for one single primary key each time the client updates a form.
For Reporting, there are a few times that a max of 20,000 records will be selected.
The user base is under 100 total users with a typical load of 10 to 30 full-time.
The SQL Server is a dedicated server quad with excessive RAM that rarely hits 15% resources under loaded conditions.
Total DB size is under 200 MB. Rule Engine Hot Spots (Tables checked) is under 20 MB.
This is not a Transaction DB application. It is for compliance and regulation tracking.
A kind of Dewy Decimal System for tracking Regulatory items.
For example: if a record for a Right-Of-Way classified as Federal Land is "Pending" and later changed to "Completed"
The Status can change with automation. This way, another specialist in a different part of the application is notified they have 30 days to complete a Public Notice.
July 7, 2014 at 10:05 am
CREATE FUNCTION [dbo].[R_35A](@ID_Wells int)
RETURNS int
AS
BEGIN;
DECLARE @Result int;
SELECT @Result = SIGN( COUNT(*) )
FROM Wells
WHERE (ID_Wells = @id_Wells) AND (Well_Name LIKE N'%Unit%');
Return @Result
END;
GO
CREATE FUNCTION [dbo].[R_35B](@ID_Wells int)
-- Rule 35 has part A that can be true, if not then part B must be true
-- the two of these make up Rule 35
RETURNS int
AS
BEGIN;
DECLARE @Result int;
SELECT @Result = SIGN( COUNT(*) )
FROM R_35_Result
WHERE (ID_Wells = @id_Wells);
Return @Result
END;
GO
SQL Server View:
SELECT ID_Wells, Well_Name, dbo.R_35(ID_Wells) AS Fed_Involvement
FROM dbo.Wells
Client Side SQL:
SELECT R_35_Testing.ID_Wells, R_35_Testing.Well_Name, R_35_Testing.R_35
FROM R_35_Testing
WHERE (((R_35_Testing.ID_Wells)=1798));
Report:
A Report is:
A Linked Table to a SQL Server View.
On the MS Access side the Query is Select MyLinkedTable Where (additional filters ).
The client uses SQL Server Native Client 11.0. The client filters on the Select statement where clause is very efficient.
From this point VBA Excel Object Code Automation is used on the Client Side to consume the data into an Excel document that contains filters, custom functions, and other advanced features for the user.
Observation: A select query for R_35 with a Where statement and sort on a non-indexed string returns 10,000 rows in well under a second.
With a view of the data fields and the VBA function on the client side, it was about 25 seconds.
The most common use of this on the client side will be to pass in one single ID_Wells and return a single true/false.
July 7, 2014 at 10:45 am
Is the value from Id_Wells on Wells table unique? Or can it have duplicates?
July 7, 2014 at 11:23 am
Mile Higher Than Sea Level (7/7/2014)
CREATE FUNCTION [dbo].[R_35A](@ID_Wells int)
RETURNS int
AS
BEGIN;
DECLARE @Result int;
SELECT @Result = SIGN( COUNT(*) )
FROM Wells
WHERE (ID_Wells = @id_Wells) AND (Well_Name LIKE N'%Unit%');
Return @Result
END;
GO
CREATE FUNCTION [dbo].[R_35B](@ID_Wells int)
-- Rule 35 has part A that can be true, if not then part B must be true
-- the two of these make up Rule 35
RETURNS int
AS
BEGIN;
DECLARE @Result int;
SELECT @Result = SIGN( COUNT(*) )
FROM R_35_Result
WHERE (ID_Wells = @id_Wells);
Return @Result
END;
GO
SQL Server View:
SELECT ID_Wells, Well_Name, dbo.R_35(ID_Wells) AS Fed_Involvement
FROM dbo.Wells
Client Side SQL:
SELECT R_35_Testing.ID_Wells, R_35_Testing.Well_Name, R_35_Testing.R_35
FROM R_35_Testing
WHERE (((R_35_Testing.ID_Wells)=1798));
Report:
A Report is:
A Linked Table to a SQL Server View.
On the MS Access side the Query is Select MyLinkedTable Where (additional filters ).
The client uses SQL Server Native Client 11.0. The client filters on the Select statement where clause is very efficient.
From this point VBA Excel Object Code Automation is used on the Client Side to consume the data into an Excel document that contains filters, custom functions, and other advanced features for the user.
Observation: A select query for R_35 with a Where statement and sort on a non-indexed string returns 10,000 rows in well under a second.
With a view of the data fields and the VBA function on the client side, it was about 25 seconds.
The most common use of this on the client side will be to pass in one single ID_Wells and return a single true/false.
What is the table or view R_35_Result in the function [dbo].[R_35B]?
July 7, 2014 at 1:01 pm
Id_Wells on Wells table is an PK (unique)
I totally forgot to include R_35 - thanks for the reminder.
As per my Missive above...
This Discrete Rule calls 2 sub-Discrete Rules.
While that might seem somewhat redundant, there are other Discrete Rules that call many multiple sub-Discrete Rules.
For example, some will have Case statement. Example: If in ND and Native Land
ALTER FUNCTION [dbo].[R_35](@ID_Wells int)
RETURNS int
AS
BEGIN;
DECLARE @Result as int;
set @Result = (SELECT dbo.R_35A(ID_Wells) AS R_35A
FROM dbo.Wells where ID_Wells = @ID_Wells);
if @Result =1
set @Result = -1-- -1 is True in Access
else
begin
set @Result = (SELECT dbo.R_35B(ID_Wells) AS R_35B
FROM dbo.Wells where ID_Wells = @ID_Wells);
if @Result =1
set @Result = -1-- -1 is True in MS Access (The client application)
else
set @Result =0 -- 0 is False in Access
end
return @result
END;
July 7, 2014 at 1:17 pm
The problem is that you're still thinking of procedural programming, there's nothing wrong on thinking like that, but you should change it when working with SQL. If you use a function, you'll read the table at least twice when you have your information in the same place.
Your view can be run without functions.
SELECT ID_Wells,
Well_Name,
CASE WHEN ID_Wells NOT LIKE N'%Unit%' THEN -1 --True in Access
WHEN EXISTS(SELECT 1
FROM R_35_Result
WHERE ID_Wells = @id_Wells) THEN -1 --True in Access
ELSE 0 --False in Access
END AS Fed_Involvement
FROM dbo.Wells
For a complete performance overview, please post table and indexes definitions and execution plans.
July 7, 2014 at 3:31 pm
Thanks again. That totally right form my general, views, transaction and bulk process.
I am with you 100%.
For a compliance rule engine, the auditors and subject matter experts understand the atomic functions and the higher level collections.
They don't want to see the code, but they really appreciate the T/F response for all the multiple steps that make up a compliance audit.
These same functions are reusable from different points of views. Changes in regulations can be pinpointed to a specific functions.
Eventually, I will build a user interface that allows the compliance manager to re-program the atomic functionality of the rules that automate the application.
But, I totally appreciate your suggestions.
I am just getting back into using SQL for more than its tables and views.
The patience with Newbies on this Forum is absolutely the best.
After going to many other sites, this is by far the best forum.
July 7, 2014 at 4:19 pm
Mile Higher Than Sea Level (7/7/2014)
Id_Wells on Wells table is an PK (unique)I totally forgot to include R_35 - thanks for the reminder.
As per my Missive above...
This Discrete Rule calls 2 sub-Discrete Rules.
While that might seem somewhat redundant, there are other Discrete Rules that call many multiple sub-Discrete Rules.
For example, some will have Case statement. Example: If in ND and Native Land
ALTER FUNCTION [dbo].[R_35](@ID_Wells int)RETURNS int
AS
BEGIN;
DECLARE @Result as int;
set @Result = (SELECT dbo.R_35A(ID_Wells) AS R_35A
FROM dbo.Wells where ID_Wells = @ID_Wells);
if @Result =1
set @Result = -1-- -1 is True in Access
else
begin
set @Result = (SELECT dbo.R_35B(ID_Wells) AS R_35B
FROM dbo.Wells where ID_Wells = @ID_Wells);
if @Result =1
set @Result = -1-- -1 is True in MS Access (The client application)
else
set @Result =0 -- 0 is False in Access
end
return @result
END;
The function above was in your initial post. What I was asking about is the target of the FROM clause in the following function:
CREATE FUNCTION [dbo].[R_35B](@ID_Wells int)
-- Rule 35 has part A that can be true, if not then part B must be true
-- the two of these make up Rule 35
RETURNS int
AS
BEGIN;
DECLARE @Result int;
SELECT @Result = SIGN( COUNT(*) )
FROM R_35_Result -- <<<<< This, it is either a table or view but we don't know what
WHERE (ID_Wells = @id_Wells);
Return @Result
END;
GO
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply