Re: Query Kills webserver but SQL server runs fine?
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 12/10/04
- Next message: Louis Davidson: "Re: Newsgroup name"
- Previous message: Louis Davidson: "Re: Need help with query to group"
- In reply to: JP: "Query Kills webserver but SQL server runs fine?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 9 Dec 2004 21:36:04 -0500
>I have a query that looks for addresses in a table and retrieves any of the
> addresses that are LIKE the% @parameters% they specified. Since the
> Addresses
> are stored in an Attribute driven table, the Address itself is stored as
Attribute driven table?
> Address1 | Address2 | City | State | Zip
>
> all in a varchar(1000) field. In C# I look for the | and format it for
> display.
So much for the first normal form :) Whining aside, the reason for the
first normal form is just the kind of thing you are dealing with. What you
need to do is build your table like this (each are columns):
Address1
Address2
City
State
Zip
Address1 + ' | ' + Address2 + ' | ' + City + ' | ' + State + ' | ' + Zip as
FormattedAddress
or just do this in a view.
> SQL doesn't use Indexes on a LIKE?
It does, unless the first character is a wildcard and the following
characters are not wildcards. And when you do this in a stored procedure,
you are going to get a very bad plan indeed.
If you split the values into different pieces, you would have a far better
chance of getting a good plan, since the only common problem would be
Address1, such as when you are looking for only a street. Breaking the
Address1 and Address2 down further would be even better.
Also, you can then formulate most queries as likes that don't start with a
wildcard:
where (@address1 = '%' or address1 like @address1)
and (@address2 = '%' or address2 like @address1)
etc.
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "JP" <JP@discussions.microsoft.com> wrote in message news:F7FD36FE-304C-4DD8-81E9-F7CF2039B757@microsoft.com... >I have a query that looks for addresses in a table and retrieves any of the > addresses that are LIKE the% @parameters% they specified. Since the > Addresses > are stored in an Attribute driven table, the Address itself is stored as > > Address1 | Address2 | City | State | Zip > > all in a varchar(1000) field. In C# I look for the | and format it for > display. > > The problem lies in when I exec the SP at the bottom. If I run in > Enterprise > Manager or .NET server explorer the SP run great and comes back usually in > under 10 secs. (Anywhere for 10 to 100 recs) > > If I run inside the application then it never returns the results and > locks > up not only the application, but out test web server comes to a crawl. > But even after I run it though the application, SQL will still run it just > fine. Only our web server is affected and has to be restarted. > > Could it be b/c I have LIKE '%@var%' for all parts of the address but the > entire address is stored concatenated? (Address1|Address2|City|State|Zip) > SQL doesn't use Indexes on a LIKE? > > If I search just Address1 it will come back pretty quick in the app but > searching for City or Zip just kills the app and the web server but SQL is > fine. I set > SET NOCOUNT ON > SET ARITHABORT ON > > Also what's the difference using CONTAINS instead of LIKE? Would this > solve > the problem? I can't run any test of my App at the moment b/c then the > other > programmers would get mad LOL. We've already had to restart 4x today we me > just trying to find the problem > > Any Idea? > > SELECT AttID, SubType,Attribute > FROM dbo.NAMEAttributes > WHERE (dbo.NAMEAttributes.Type = 'Address') and > ( > (@Address1!='' AND dbo.NAMEAttributes.Attribute LIKE @Address1) AND > (@Address2!='' AND dbo.NAMEAttributes.Attribute LIKE @Address2) AND > (@City!='' AND dbo.NAMEAttributes.Attribute LIKE @City) AND > (@State!='' AND dbo.NAMEAttributes.Attribute LIKE @State) AND > (@ZipCode !='' AND dbo.NAMEAttributes.Attribute like @ZipCode) > ) > ORDER BY Attribute > > -- > JP > .NET Software Developer >
- Next message: Louis Davidson: "Re: Newsgroup name"
- Previous message: Louis Davidson: "Re: Need help with query to group"
- In reply to: JP: "Query Kills webserver but SQL server runs fine?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
