|
Hi everyone,
I have a view with multiple outer joins and a on a lot of the fields I'm invoking a scalard functions.
The result is a very poor performance. I've been browsing the internet quite some time now (also read the article: Using Table-Valued Functions in SQL Server) but I can't seem to manage it on my own.
I know the difference between SVF and TVF and that's why I want to use TVF.
This is the Scalard function I want to recreate into a Table Valued Function:
The Shipping unit of my articles must be divided by 100 except for articles < 99999 or article 4859696
USE [Staging]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Devide_Shipping_Unit]
(
@ValueString nvarchar(8),
@ArticleString nvarchar(8)
)
RETURNS Int
AS
BEGIN
DECLARE @result int
IF ISNUMERIC(@ValueString)<> 1 OR ISNUMERIC(@ArticleString)<> 1
BEGIN
set @result = 0
END
ELSE
BEGIN
if Cast(@ArticleString AS int) <= 99999 or @ArticleString = '4859696'
set @result = 1 --CAST(@ValueString AS int) / 1000
Else
set @result =CAST(@ValueString AS int) / 100
END
return @result
END
GO
Another scalar function is a conversion of dates to a numeric datatype:
ALTER FUNCTION [dbo].[CONVERT_DATE_TO_NUMERIC]
(
@dateValueString nvarchar(10)
)
RETURNS Numeric(8, 0)
AS
BEGIN
DECLARE @result Numeric(8, 0)
--DECLARE @dateValueString nvarchar(10) = '03/12/2013'
DECLARE @startdate nvarchar(10) = ''
-- set @dateValueString = '2013-07-23'
set @dateValueString = REPLACE(@dateValueString,'/','')
set @dateValueString = REPLACE(@dateValueString,'-','')
declare @_year nvarchar(4) = LEFT(@dateValueString,4)
declare @_month nvarchar(2) = SUBSTRING(@dateValueString,5,2)
declare @_day nvarchar(2) = RIGHT(@dateValueString,2)
set @startdate = @_year + @_month + @_day
IF ISNUMERIC(@startdate)<> 1
BEGIN
set @result = 0
END
ELSE
BEGIN
set @result = @startdate END
return @result
END
GO
Can anybody please help me I'm stuck 
Ambertje
|
|
|
|
|
Have you used SQL Profiler to identify the expensive parts of the query. It is much more likely you are missing indexes. Try that before dumping on the scalar functions.
A scalar that does not reference another database object (eg looks up a table) is not going to have a great deal of impact.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm working with Microsoft SQL Server Management Studio, I've searched for the profiler but can't find it.
Do you mean an SQL Server Profiler?
Can you give me an example or a link to where I can find that please?
This is what I get when I Set Statistics IO and Time ON:
Table 'Worktable'. Scan count 252, logical reads 20839, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AD'. Scan count 1, logical reads 109674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'D_Article'. Scan count 1, logical reads 729, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'porcKD'. Scan count 1, logical reads 1473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 91947 ms, elapsed time = 119500 ms.
I wanted to create indexes on the View but a View doesn't allow to create indexes.
The view gets the data out of the bottom (STAGING) area in the form of text files.
The View is used to load the data through SSIS in TOP area (DWH)
No table in the Staging area contains indexes.
|
|
|
|
|
Ambertje wrote: a View doesn't allow to create indexes
Yes it does[^]. You just need to make sure the view is created WITH SCHEMABINDING.
However, to properly use an indexed view on any edition lower than SQL Enterprise, you'll need to use the WITH ( NOEXPAND ) query hint.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
My view contains aggregates, outer joins, subqueries so With Schemabinding wont work
|
|
|
|
|
Ambertje wrote: The view gets the data out of the bottom (STAGING) area in the form of text files.
Ah I thought you were doing something sensible, expecting performance from text files is not going to work.
I suggest you bulk copy the text files into staging tables, do attempt to apply transformations, and then use a stored proc to do the transforms from staging to your destination.
I would expect an order of magnitude performance gain with proper tuning of the staging indexes and procedure design. If the data is extreme you may want to look into dropping staging indexes before the insert and reimplementing them after import.
Doing transforms on the way in is a really lousy design, I know SSIS and all the tools do this but it is just wrong. Extract, Load, Transform
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try adding WITH SCHEMABINDING to your functions, between the RETURNS <type> and As lines.
http://www.sqlservercentral.com/blogs/sqlstudies/2014/09/15/i-schemabound-my-scalar-udf-and-you-wont-believe-what-happened-next/[^]
ISNUMERIC is not reliable. All of the following values will be considered numeric, but cannot be cast to an integer:
- ISNUMERIC('-')
- ISNUMERIC('.')
- ISNUMERIC('-$.')
If you're using SQL 2012 or higher, use the new TRY_PARSE function[^] instead. Otherwise, test that the string doesn't contain any non-numeric characters:
If @ValueString Like '%[^0-9]%' Or @ArticleString Like '%[^0-9]%'
Begin
set @result = 0
End
Assuming you're starting with a date or datetime / datetime2 type, your CONVERT_DATE_TO_NUMERIC function can be replaced with:
Convert(int, Convert(char(8), YourDateColumn, 112))
Passing 112 to the Convert function[^] formats the date as yyyyMMdd. Converting that to an integer gives the same result as your function.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm going to suggest something weird.
It looks as if you are using the scalar function to convert a single column into another. That could also be done before your query is executed, and that "might" save time. Might, as I haven't tested it 
Add the required int-column for the date, and run an update-query to do the conversion. Your table is now one column larger, but lost a calculation. You could keep the converted date consistent by updating them over a trigger when a record is inserted/modified. If you're not allowed to modify the original table, you can always create a new table and add the primary key of the original.
That way you can eliminate the need for the function completely, and move the calculation to the moment the records is inserted/updated. That delay may be hardly noticeable when manipulating a single record, while it adds up to a lot of calculations if it has to be done "on the fly".
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I am making a stored procedure in "SQL SERVER 2008" for "item allotment" Which has a different items and has to assign to particular user.
What I am doing is, A table has a three columns 1-Total_item,2-Allotted_item and 3-Available_item , When i click the button ASSIGN it has to assign the item to particular User and the available item must have to be shown in Available_item column
Suppose---Before allotment
Total_item Allotted_item Available_item
10 0 0
8 0 0
Now After Allotment----
Total_item Allotted_item Available_item
10 1 9
8 2 6
I am going wrong Please help How could I make This happen
Thanks
|
|
|
|
|
How does your item table look like?
Wrong is evil and must be defeated. - Jeff Ello
Any organization is like a tree full of monkeys. The monkeys on top look down and see a tree full of smiling faces. The monkeys on the bottom look up and see nothing but assholes.
|
|
|
|
|
I HAVE 2 TABLES
TABLE 1 = "ITEM ALLOTMENT" it has initially "total_item" = 10 or some other value, "allotted_item" = 0 and "item_available" = 0
CONSISTS OF COLUMNS---
ITEM_NAME NVARCHAR(50)
TOTAL_ITEM--- INT
ALLOTTED_ITEM---- INT
ITEM_AVAILABLE--- INT
AND
TABLE 2 = ITEM it only stores the allocation details after clicking assign button
CONSISTS OF COLUMNS---
ITEM_ID (PRIMARY KEY)
PRODUCT_NO -- nvarchar(50)
ITEM_NAME -- nvarchar(50)
now after allotment details are filled in "ITEM" table that this "item_name" is allocated to this "product_no" and what is has to done in "item allotment" table is just after allotment its value must be updated that means column name
"allotted_item" value must be increased and in "item_available" column the subtracted value is have to shown example
If in "total_item" there is 10 items stored after allocation "allotted_item" value will be increased to 1 and subtracted value will be shown in "item_available" column
all these have to be done on assign button click. So, I that i am using Stored Procedure
Please help me out ,I am failing to which.
Thanks
|
|
|
|
|
What is problem you are facing?, You should use the Begin and end trans block and update both tables in the same block on click of assign button.
|
|
|
|
|
The Problem is in deceasing the value after assigning item.
|
|
|
|
|
What is the command/Query you are using to do so and what issue do you face.
|
|
|
|
|
This isn't normalized.
I'd suggest that you drop table1 and add an allotted column to table2 which contains to whom it's allotted (null or ID).
Then you use a query to get the state of allottments, such as:
SELECT ITEM_NAME
,Count(ITEM_ID) Total_item
,Count(Alloted) Allotted_item
,Count(ITEM_ID) - Count(Alloted) Available_item
FROM Table2
GROUP BY ITEM_NAME
Wrong is evil and must be defeated. - Jeff Ello
Any organization is like a tree full of monkeys. The monkeys on top look down and see a tree full of smiling faces. The monkeys on the bottom look up and see nothing but assholes.
|
|
|
|
|
I have a table with students' details and email address and I send email to them
I want to see the logs for each student if it was sent successfully or not. for example:
STUD_1 student1@email.com failed
STUD_2 student2@email.com sent
Can we do it like this? The idea is to get the sent_status from
sysmail_mailitems (or any other accurate/relevant source) for each student.
|
|
|
|
|
If I understand the question correctly, you can use a query like
SELECT mailitem_id,
recipients,
subject,
sent_status
FROM sysmail_allitems
For table description, see sysmail_allitems[^]
|
|
|
|
|
can we link sysmail_allitems to my student table? to see if the email was sent or not to a particular student?
|
|
|
|
|
If the students table contains the email address, then you can use that to check if the recipient is equal to student.email. If multiple recipients can be found ind recipients field then you can join using a string search with CHARINDEX function.
So either something like:
SELECT ...
FROM sysmail_allitems sai,
student s
WHERE LOWER(sai.recipients) = LOWER(s.email)
or
SELECT ...
FROM sysmail_allitems sai,
student s
WHERE CHARINDEX(LOWER(s.email), LOWER(sai.recipients)) > 0
|
|
|
|
|
It's usually cleaner to use the ANSI JOIN syntax, so that you keep the join conditions separate from any filter conditions.
SELECT
...
FROM
sysmail_allitems sai
INNER JOIN student s
ON sai.recipients = s.email
Also, most databases don't use a case-sensitive collation, so there shouldn't be any need to use the LOWER function here.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks for the suggestion
Richard Deeming wrote: It's usually cleaner to use the ANSI JOIN syntax
I usually use ANSI join but in this case I felt it would be more understandable to use the 'old' syntax because the variations for the join itself are quite different.
Richard Deeming wrote: most databases don't use a case-sensitive collation, so there shouldn't be any need to use the LOWER function here.
Not sure about this so I wanted to be on the safe side.
I should have written these reasons to the original post in the first place.
|
|
|
|
|
Hi, I've been asked to write a report which lists the average time it takes for a helpdesk job to be closed. There isn't a closed date as such, the table column is simply an audit date/time which can be for various tasks.
I need to use this column along with another which is an ID of the action. For example:
User TaskID Task AuditDatetime
Bob 2 Opens job 1/8/2014 11:14:29
Bob 5 Closes job 2/8/2014 10:12:26
Joe 2 Opens job 2/8/2014 11:01:45
Bob 5 Closes job 3/8/2014 11:44:22
My SQL is very rusty, I'm sure the report is technically possible but I can't for the life of me figure out what the SQL is. Can anybody please help?
I'm aware of the SQL AVG function and also know I may need to GROUP BY the user. I'm thinking some kind of CASE statement perhaps but again I can't think of the SQL to achieve this...
|
|
|
|
|
Try something like this:
select avg(diff)
from
(
select datediff(d,h1.auditdatetime,h2.auditdatetime) diff
from helpdesk h1
join helpdesk h2
on h1.taskid = h2.taskid
and h2.task = 'closes job'
where h1.task = 'opens job'
)x
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
This can be done in various ways but one way could be to use a small scalar query in the select statement. Something like:
SELECT User AS OpenedBy,
AuditDatetime AS StartTime,
DATEDIFF(hh, AuditDatetime, ( SELECT MIN(AuditDatetime)
FROM TheTable tt2
WHERE tt2.AuditDatetime > tt.AuditDatetime)
)AS DurationInHours
FROM TheTable tt
WHERE tt.Task = 'Opens job'
Using a query like the above you can get the open/close pair data and you can use that as an inline view. So something like
SELECT AVG(DurationInHours)
FROM (SELECT User AS OpenedBy,
AuditDatetime AS StartTime,
DATEDIFF(hh, AuditDatetime, ( SELECT MIN(AuditDatetime)
FROM TheTable tt2
WHERE tt2.AuditDatetime > tt.AuditDatetime)
)AS DurationInHours
FROM TheTable tt
WHERE tt.Task = 'Opens job') iv
Probably I made a ton of typos but hope you get the idea
[EDIT]
Just noticed that GuyThiebaut wrote almost same kind of answer at the same time. If the value of TaskId can be used to connect the open/close operations then his approach is better.
The reason I used the date field is that in your example data the same TaskId was opened twice so my interpretation was that the TaskId isn't unique among tasks:
User TaskID Task AuditDatetime
Bob 2 Opens job 1/8/2014 11:14:29
Bob 5 Closes job 2/8/2014 10:12:26
Joe 2 Opens job 2/8/2014 11:01:45
Bob 5 Closes job 3/8/2014 11:44:22
|
|
|
|
|
Hi Mike - I made the assumption that the taskid is the key and that one person may open the task while another may close it.
I don't think we have been given enough information to solve this without guessing 
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
