February 20, 2009 at 12:02 am
February 20, 2009 at 12:16 am
Probably that sql statement is being generated dynamically.
Some bright minds use it in their starting where clause because they don't know if other conditions will be added.
It guarantees the where clause will work, but doesn't have an effect on the actual query.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2009 at 12:32 am
February 20, 2009 at 1:18 am
karthikeyan (2/20/2009)
The statement like......select top 100 * from xyz
where 1=1
AND (sid IN ( 111 ) or oid IN ( 089 ) )
and timein >= N'02/02/2009 09:00:38' and timein <= N'02/02/2009 10:58:00'
sid and oid are varchar datatype columns.
Thanks,
Probably this query comes from a "chose your arguments" panel.
In many cases they build this kind of statements using 4 parts.
1) the actual select "Select top 100 * "
2) the table section (including optional joins) " from xyz "
3) The where clause starting with "where 1 = 1 " to be sure the where clause always work. and adding other condition later on to the where clause string ... "AND (sid IN ( 111 ) or oid IN ( 089 ) )
and timein >= N'02/02/2009 09:00:38' and timein <= N'02/02/2009 10:58:00'"
4) the Order by section is missing in your case.
Then executing it all using :
exec ( @SelSection + @TableSection + @WhereSection + @OrderBySection )
In your query the ORDER By is missing !
This will cause data to be returned in random order !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2009 at 7:11 am
ALZDBA is probably right and the 1=1 is because of the dynamically generated SQL Select by the user interface.
My comment is that such a technique is a BAD, BAD practice. I never, ever allow any user to access any data in the database directly - all must use stored procedures.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 20, 2009 at 7:26 am
JacekO (2/20/2009)
ALZDBA is probably right and the 1=1 is because of the dynamically generated SQL Select by the user interface.My comment is that such a technique is a BAD, BAD practice. I never, ever allow any user to access any data in the database directly - all must use stored procedures.
FYI I'm currently involved in a performance quest of a system that
uses stored procedures to generate this kind of dynamic sql :sick:
Another bright gift of this dev team is the way they resolve in-list
strings:
@theCSVstring = '125,36859,8524'
where '%,'+thecolumn+',%' like '%,' + @theCSVstring + ',%'
How come this thing doesn't perform at lightning speed :hehe:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2009 at 7:38 am
ALZDBA
I think the
where '%,'+thecolumn+',%' like '%,' + @theCSVstring + ',%'
in your post should be the other way around
where '%,' + @theCSVstring + ',%' like '%,'+thecolumn+',%' 😛
I use this technique myself from time to time. And it works fine. My theory for SQL is use the tools that match the job - and no tool is wrong - just the usage.
Because you have a nail gun it does not mean you have to toss out your hammer.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 20, 2009 at 7:48 am
where string like column?
Isn't it WHERE MyCol like '%searchstring%'
I hate these types of searches. I think it's a lazy developer. It might take you more time to build a few stored procs to cover variations of searches, or even to require more information, but it will pay off in the long run. People run these things all the time, so a little more developer time pays off with much less wasted time later.
February 20, 2009 at 7:57 am
Yes Steve
string like column
It works and several times saved me a lot of coding. It is just another tool to be used. I understand that in many cases the performance of such a statement may not match the requirements but performance is not always the major factor.
And lazy developer sometimes is not a bad thing. If you think about this, laziness is the drive of progress. We want more with doing less - so we have to be more inventive.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 20, 2009 at 8:19 am
Actually the problem with that kind of like operations is that one should implement a decent split function (according to the datatype of the target column)
Then SQLServer would be able to actually use an index to resolve the in-list.
Select ...
from mytable T
inner join dbo.fn_DBA_Split_int (@csvstring, ',') F
on T.thecolumn = F.Item
the function would be a TVF like this:
/*
* Split a delimited string using a table valued function with XML
*/
-- table-valued function
CREATE FUNCTION dbo.fn_DBA_Split_int(@Data varchar(4000),
@Delimiter varchar(10) = ',')
RETURNS @tblSplit TABLE
(ItemId int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Item integer NULL)
AS
BEGIN
/*
select *
from master.dbo.fn_DBA_Split_int('1,58,31456,1888888888,987654321',',') U
select e.*
from pubs..employee E
inner join master.dbo.fn_DBA_Split_int('1,58,31456,1888888888,987654321',',') U
on E.fname = U.item
*/
DECLARE @x XML
SET @x = ' '
INSERT INTO @tblSplit (Item)
SELECT x.i.value('.', 'INTEGER')
FROM @x.nodes('//i') x(i)
RETURN
END
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2009 at 8:36 am
I guess everybody is missing my point. I even submitted a short article on this subjects few days ago and it is under review - I guess I did not properly explain how this technique works or why we need it.
The point is - we as developers, DBA or whatever we want to call ourselves need to use certain tools to get our job done. I think the more tools, tricks, techinques we know the easier it is to do our job. The trick is to match the tool for the job.
If I have to search a table that has just few thousands records I do not care about the indexes because they will probably worsen the performance rather then improve it. Or if I need to quickly write a dirty search once in a while to find something in the database and do not care how long the search takes. A second or a minute in such case makes no difference if I have to spend several minutes coding a function vs few seconds creating a 'dirty' SELECT.
Using the WHERE text like column techinque when the text is a list if separated values is a valuable addition to your toolbox. You do not have to use it everyday, but sometimes you may want to use it. Do not dismiss a tool because you do not need it on a daily basis.
What would you use to hang a picture on a wall - a nail gun or a hammer?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 20, 2009 at 9:32 am
JacekO (2/20/2009)
I guess everybody is missing my point. I even submitted a short article on this subjects few days ago and it is under review - I guess I did not properly explain how this technique works or why we need it.The point is - we as developers, DBA or whatever we want to call ourselves need to use certain tools to get our job done. I think the more tools, tricks, techinques we know the easier it is to do our job. The trick is to match the tool for the job.
If I have to search a table that has just few thousands records I do not care about the indexes because they will probably worsen the performance rather then improve it. Or if I need to quickly write a dirty search once in a while to find something in the database and do not care how long the search takes. A second or a minute in such case makes no difference if I have to spend several minutes coding a function vs few seconds creating a 'dirty' SELECT.
Using the WHERE text like column techinque when the text is a list if separated values is a valuable addition to your toolbox. You do not have to use it everyday, but sometimes you may want to use it. Do not dismiss a tool because you do not need it on a daily basis.
What would you use to hang a picture on a wall - a nail gun or a hammer?
Sure I get the point ......
The hazard is "copy/paste" is the basis of all programming stuff....
so this kind of "quick and dirty" solutions realy get into OLTP 24/7 top performance environments !
... Don't ask about QA ....;)
Yes I use stuff I wouldn't want any developer to use....
but I do not promote them and always leave a comment on top of it NOT to use it because of this or that reason.
A slingshot can also launch a nail 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2009 at 9:52 am
I have been on both sides of the fence.
Developnig being one and guarding the DB against the developers being the other.:D
I think I still am, on both, at the same time...
And you are right - most developers do not think about performace (and scalability!!!) at all.
Luckily we got a strick code review process so any SQL code has to pass by me. And you can use whatever technique you want as long as you have a valid point for using it.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 20, 2009 at 11:47 am
If I have to search a table that has just few thousands records I do not care about the indexes because they will probably worsen the performance rather then improve it.
Really? That seems like a terribly over-broad statement to be making. You say "probably". Do you ever bother to check and see what the difference is? Is the execution plan a table scan? How can an index worsen the performance of a query if the optimzer isn't even using it?
Your argument boils down to "It's gonna do a table scan anyway, so I might as well just check for a %string%."
I understand that sometimes it's quicker when you're doing an ad-hoc query to just use a simple technique because a query that runs 30 seconds is preferable to one that runs in 2 seconds but took you 5 minutes to write. But, the danger of putting such techniques in your toolbox is that they will creep into production code that's being run all day every day. If you are in a low-volume, low-intensity environment maybe this isn't a problem at the moment, but I've seen it come back and bite people.
My toolbox is where I put the fastest, cleanest, most flexible techniques I've come across.
P.S. I don't worry about 1=1 statements because I've never seen an execution plan changed by adding 1=1 to the WHERE clause. When I've timed jobs, I've never seen a difference in milliseconds significant enough to attribute to any overhead. If someone shows me some hard data, I'm ready to change my mind in a moment.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 20, 2009 at 12:19 pm
Bob,
you are right - I do not check anything, I measure time by checking a shadow of a stick and wind direction by licking my finger and sticking it out. :hehe:
Currently I work on medical instruments and sometimes we have to squeeze milliseconds from a query and sometimes droping the index makes things go faster. I can not give example right now of such cases (unfortunatelly I do not keep a library of 'this did not work') but things sometimes happen that I can not explain. We do not deal with a lot of data but sometimes too little data can cause problems as well.
I had complicated views that worked correctly when there were several records in the tables they used and did not return the correct data if there was one or two records. Why? I can not explain.
I also had SELECT queries that would kill SQL Server dead when executed. They did not run long, they just killed the service. They were not supposed to, but they did.
And I used "probably" because unless you are dealing always with the same environment you can not guarantee the same results.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply