Optimise Select Statement

Hi
I have this select statement that I need to optimise:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
The tblCustomer.Code is a string like 'STAR00000001' the function removes
the padded zeros.
The tblEvents table contains information in a string including the
contracted Customer.Code in this format "Type STAR1 ........"
Any help would be much appreciated.
Thanks
BDon=B4t know how your function works but what about this:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
LEFT(tblCustomer.Code,CHARINDEX('0',tblCustomer.Code)-1) + '%'
HTH, Jens Suessmeyer.|||"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:OsCnbqevFHA.3688@.tk2msftngp13.phx.gbl...
> Hi
> I have this select statement that I need to optimise:
> SELECT Created, Code, tblEvents.Ref
> FROM tblCustomers, tblEvents
> WHERE tblEvents.description LIKE 'Type ' +
> dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
> The tblCustomer.Code is a string like 'STAR00000001' the function removes
> the padded zeros.
No offense, but I think you need to optimise the design, not the query. Why
are you storing padded zeros if they're irrelevant or different from the
data you're actually modeling? Why are these tables quasi-related via a
string that changes?|||Hi Aaron
I would love to change the design but it is a third party product, although
i have requested the change in a future release I need a temporary solution.
Thanks
B
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#d7CV1evFHA.2292@.TK2MSFTNGP12.phx.gbl...
> "Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
> news:OsCnbqevFHA.3688@.tk2msftngp13.phx.gbl...
removes
> No offense, but I think you need to optimise the design, not the query.
Why
> are you storing padded zeros if they're irrelevant or different from the
> data you're actually modeling? Why are these tables quasi-related via a
> string that changes?
>|||On Tue, 20 Sep 2005 14:51:05 +0100, Ben wrote:

>I have this select statement that I need to optimise:
>SELECT Created, Code, tblEvents.Ref
>FROM tblCustomers, tblEvents
>WHERE tblEvents.description LIKE 'Type ' +
>dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
>The tblCustomer.Code is a string like 'STAR00000001' the function removes
>the padded zeros.
>The tblEvents table contains information in a string including the
>contracted Customer.Code in this format "Type STAR1 ........"
>Any help would be much appreciated.
Hi Ben,
User-defined functions can be slow. If possible, use builtin functions
that achieve the same effect.
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE
'Type ' + REPLACE(tblCustomer.Code, '0', '') + '%'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jens
Would this work for codes such as:
STAR00000102?
Thanks
B
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127225495.861651.299360@.g47g2000cwa.googlegroups.com...
Dont know how your function works but what about this:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
LEFT(tblCustomer.Code,CHARINDEX('0',tblCustomer.Code)-1) + '%'
HTH, Jens Suessmeyer.|||DECLARE @.String varchar(2000)
SEt @.String = 'STAR00000102'
SELECT LEFT(@.String,CHARINDEX('0',@.String)-1) + '%'
results in "STAR%"|||Hi Jens
Thanks for your post.
The problem is that we have codes stored in tblEvents.description as STAR1,
STAR2, STAR3......STAR102, STAR103
and for example we need to distict "Type STAR103 ...." from "Type STAR1
...."
Thanks
B
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127294257.162562.55160@.z14g2000cwz.googlegroups.com...
> DECLARE @.String varchar(2000)
> SEt @.String = 'STAR00000102'
> SELECT LEFT(@.String,CHARINDEX('0',@.String)-1) + '%'
> results in "STAR%"
>|||That=B4s not easy, there has to be a delimiter or something where you
can tell that the trailing zeros start, how do you want to differ
perhaps
STARS100 and STARS1 ?|||On Wed, 21 Sep 2005 09:36:47 +0100, Ben wrote:

>Hi Jens
>Would this work for codes such as:
>STAR00000102?
Hi Ben,
I assume that this has to be "shortened" to STAR102?
DECLARE @.a varchar(20)
SET @.a = 'STAR00000102'
SELECT STUFF(@.a, CHARINDEX('0', @.a),
PATINDEX('%[1-9]%', @.a) - CHARINDEX('0', @.a), '')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)