Can I use SQL instead of cursor?

Hi,

I'm currently converting a VB function to SQL-Server. The function uses a cursor to find the "terms of delivery" (TOD) with the highest priority.

I have a table with articlenumber, tod (and lots of other columns that doesn't matter now)

ABC123 , AFG
ABC123 , AFG
ABC123 , BGH
ABC123 , BGH
ABC123 , CDD

"CDD" has the highest priority and therefore ALL with the same articlenumber should use that tod.

The existing function uses a cursor and loops through a recordset and updates every row with the same articlenumber as the current row with the tod with the highest priority (of the ones read) with the same articlenumber.

One update per row takes "forever" to run...

I figured it would be possible to select the tod with the highest priority for one articlenumber into a temp table and then do ONE update to set the tod on all rows...Here is a quick and dirty. It could be improved with a little work:



create table foo (ArtNo varchar(25), pri varchar(25))
insert into foo values ('ABC123' , 'AFG')
insert into foo values ('ABC123' , 'AFG')
insert into foo values ('ABC123' , 'BGH')
insert into foo values ('ABC123' , 'BGH')
insert into foo values ('ABC123' , 'CDD')
insert into foo values ('ABCD123' , 'AFG')
insert into foo values ('ABCD123' , 'AFG')
insert into foo values ('ABCD123' , 'BGH')
insert into foo values ('ABCD123' , 'BGH')
insert into foo values ('ABCD123' , 'CDE')
select ArtNo, max(pri) pri
into #temp
from foo
group by ArtNo
update foo
set foo.pri = #temp.pri
from foo,#temp
where foo.Artno = #temp.ArtNo

select * from foo
drop table foo|||Hmm..

What if BGH has the highest priority?

The priority is not determined by the alphabet. :-(|||Then you will need a table or some other method to determine an ordered set of priorities.

As a start, look at how you do it now.|||I'm at home now, but something struck me on the way home...

If I change the three letter combinations to numbers according to their priority, I can then use:

SELECT articlenumber, MAX(tod)
FROM mytable
GROUP BY articlenumber

into a temptable..

The I use the temptable and updates ALL articlenumbers with their right tod.

I'll try it tomorrow. Clock is 5 in the afternoon in sweden now. :-)|||I'm at home now, but something struck me on the way home...

If I change the three letter combinations to numbers according to their priority, I can then use:

SELECT articlenumber, MAX(tod)
FROM mytable
GROUP BY articlenumber

into a temptable..

Then I use the temptable and updates ALL articlenumbers with their right tod.

I'll try it tomorrow. Clock is 5 in the afternoon in sweden now. :-)|||I'm at home now, but something struck me on the way home...

If I change the three letter combinations to numbers according to their priority, I can then use:

SELECT articlenumber, MAX(tod)
FROM mytable
GROUP BY articlenumber

into a temptable..

Then I use the temptable and updates ALL articlenumbers with their right tod.

I'll try it tomorrow. Clock is 5 in the afternoon in sweden now. :-)|||Worked like a charm.. My solution:

--

/* Get priorities of tod */
UPDATE mytable
SET tod =
CASE tod
WHEN 'BGH' THEN '1'
WHEN 'AFG' THEN '2'
WHEN 'CDD' THEN '3'
ELSE '999'
END

/* Create temptable */
create table dbo.temp_table (articlenumber varchar(31), PRIO varchar(10))

/* Make temporary list with "highest" TOD */
insert into dbo.temp_table (articlenumber, PRIO)
select articlenumber,
CASE MIN(cast(tod AS INT))
WHEN '1' THEN 'BGH'
WHEN '2' THEN 'AFG'
WHEN '3' THEN 'CDD'
ELSE 'N/A'
END
from mytable
GROUP BY articlenumber

/* Update ALL to highest tod */
UPDATE mytable
SET tod = b.PRIO
FROM mytable a, dbo.temp_table b
WHERE
(a.articlenumber = b.articlenumber)

--

Can I make my clustered index scan cheaper?

Hi,
I have a query here that I would like to make cheaper in terms of query
cost. Here's the query:
________________________________________
__________________________
Select distinct
vw.CustomerID,
vw.BranchID,
vw.SourceOfBusiness ,
vw.SpkCustNo,
vw.Surname,
vw.Initials,
vw.Incode,
vw.Outcode,
vw.Updated
>From tbl_Insurer i
Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
Where i.id = @.InsurerID
and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
@.StartDate
and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
and (@.BranchID is null or vw.BranchID = @.BranchID)
and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
and (@.Surname is null or vw.Surname like @.Surname)
and (@.Initials is null or vw.Initials = @.Initials)
and (@.Incode is null or vw.Incode = @.Incode)
and (@.Outcode is null or vw.Outcode = @.Outcode)
and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
Order by vw.Surname, vw.Initials, vw.BranchID
________________________________________
__________________________
This takes 14 seconds to return 25 or so rows, I would like to knock
the return time down to under 5 seconds. When I display the Execution
Plan, I can see that the most expensive item is a clustered index scan
on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
Can I know alter my query to reduce that cost?
Thanks
qhWhat is the clustered index?
A clustered index s is faster than a scan but relies on only looking
at a portion of the index.
If the renewaldate were the clustered index that may help.
Martin
Scott wrote:
> Hi,
> I have a query here that I would like to make cheaper in terms of query
> cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
>
> Can I know alter my query to reduce that cost?
>
> Thanks
> qh
>|||Martin Selway wrote:
> What is the clustered index?
The clustered index on the view is PolicyID. I have read that because
I am doing a search for null for every element in the where clause
these aren't picked up by the clustered index.
I could be wrong though...
Thanks
qh|||Don't do this...
convert(datetime, convert(char(11), vw.RenewalDate, 103), 103)
Do the convert on @.Start instead.
The convert will negate useful usage of an index (instead a full index scan
at best).
It looks like a query built from a search, i'd recommend building it up
using dynamic SQL so you don't have this...
and (@.Surname is null or vw.Surname like @.Surname)
but instead have something that can use an index properly...
and vw.Surname like @.Surname
You can then build up some nice indexes based on popular inputs from the
users.
That will significantly improve the performance of the query.
To prevent injection, use parameterisation and sp_executesql, for
instance...
declare @.nsql nvarchar(4000)
set @.nsql = 'select.... where vw.Surname like @.Surname'
exec sp_executesql @.nsql,
N'@.Surname varchar(...),
@.Surname
Hope that helps.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1129713424.015770.212820@.o13g2000cwo.googlegroups.com...
> Hi,
> I have a query here that I would like to make cheaper in terms of query
> cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
>
> Can I know alter my query to reduce that cost?
>
> Thanks
> qh
>|||Scott wrote:
> Hi,
> I have a query here that I would like to make cheaper in terms of
> query cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
You can create a text version of the plan with QA so we can have a look.

> Can I know alter my query to reduce that cost?
What indexes are in place? It seems all your criteria are on the view so
the view definition and the other DDL would be interesting to look at.
Some other remarks:
- you use like, that's usually slow especially if used with a leading
wildcard
- you convert vw.RenewalDate back and forth and then compare it. Try to
find a solution where @.StartDate
is converted instead or where @.StartDate is adjusted so it can be used
directly.
- do you have proper indexes on the join columns in place?
Kind regards
robert|||Never use functions on columns. That leads to table scan/index scan and inde
x
s is faster.
--
Regards
R.D
--Knowledge gets doubled when shared
"Tony Rogerson" wrote:

> Don't do this...
> convert(datetime, convert(char(11), vw.RenewalDate, 103), 103)
> Do the convert on @.Start instead.
> The convert will negate useful usage of an index (instead a full index sca
n
> at best).
> It looks like a query built from a search, i'd recommend building it up
> using dynamic SQL so you don't have this...
> and (@.Surname is null or vw.Surname like @.Surname)
> but instead have something that can use an index properly...
> and vw.Surname like @.Surname
> You can then build up some nice indexes based on popular inputs from the
> users.
> That will significantly improve the performance of the query.
> To prevent injection, use parameterisation and sp_executesql, for
> instance...
> declare @.nsql nvarchar(4000)
> set @.nsql = 'select.... where vw.Surname like @.Surname'
> exec sp_executesql @.nsql,
> N'@.Surname varchar(...),
> @.Surname
> Hope that helps.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
> news:1129713424.015770.212820@.o13g2000cwo.googlegroups.com...
>
>|||Cheers for all the replies. Tony R. managed to spot that fact that this
query used to be built using dynamic sql however the powers that be
came over all security conscious and said it had to be built this way.
Thanks Again
Scott|||That's not strictly true.
You should think carefully about using a function on a column that you've
indexed (the access path), you can use a function on the value column...
Index on RenewalDate...
where RenewalDate = datediff( day, -1, myothercolumn )
The above is fine, and the index will be used efficiently.
You can also use functions to good effect in the SELECT clause and ORDER
clause.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6C2CF2D1-4BDB-4634-A8E5-8F477FC14378@.microsoft.com...
> Never use functions on columns. That leads to table scan/index scan and
> index
> s is faster.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Tony Rogerson" wrote:
>|||Bizzarre,
They haven't really understood how injection happens then which is even more
scary!
I hope they are using the command object in the app code rather than
concatenating a string together and executing it.
I'd go back to the powers at be and tell them in order to get good
performance and scalability you need to either write out a ton of IF ELSE
statements or use safe, parameterised dynamic SQL.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1129718265.957237.248090@.g47g2000cwa.googlegroups.com...
> Cheers for all the replies. Tony R. managed to spot that fact that this
> query used to be built using dynamic sql however the powers that be
> came over all security conscious and said it had to be built this way.
> Thanks Again
> Scott
>|||This is the view:
CREATE VIEW dbo.vw_SearchCustomerPolicy WITH SCHEMABINDING
AS
SELECT
c.ID AS CustomerID,
c.BranchID,
c.SourceOfBusiness,
c.SpkCustNo,
c.Surname,
c.Initials,
c.Incode,
c.Outcode,
c.Updated,
p.ID AS PolicyID,
p.PolicyRef,
p.SpkPolNo,
p.RenewalDate,
p.SchemeID
FROM dbo.tbl_CA_Customer c
INNER JOIN dbo.tbl_CA_Policy p ON p.CA_CustomerID = c.ID
And this is the text version of the execution plan.
Regards,
qh