|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stumped on some code...need to setup a way to set a territory location status. We have two territories - LA and NY... LA Territory - California Customers & Leads. NY Territory - Customers & Leads in Connecticut, Maine, Massachusetts, New Hampshire, New Jersey, New York, Pennsylvania, Rhode Island, Vermont. I've got tables that store State, Area Codes and Zipcodes. I've got a bit field setup to flag the 9 states, 33 Area Codes, and 7,564 Zipcodes for these states. We don't always get complete data, so we have to check all of these variables to see what area the customer or lead belongs to. I've tried using a UDF & passing in the State, Area, Zip of the customer/lead and returning what territory the cust/lead belongs to. I've tried having a case statement. When I setup the UDF in the search query, our sql server came to a screamin halt after the memery used by sql maxed out our server... it jumped from only usin a few megs to just over two gigs, then our server crashed... so I figured that UDF's are good but not for bulk-results... I've tried usin the case statement, but it increases the query time from 4 seconds to 30 seconds. I've tried checking if the area/zip codes matchup using "IN" and "EXISTS" tags... I'm at a loss... seems like whatever I do, the queries go from quick to crawl. Any fresh idea's on how to grab this sorta data quickly would be much appreciated. TheDarkFrag***@gmail.com wrote:
Show quote > ok, so I'm workin on a customer & leads part of this application. I Why are you encoding data in "bit fields" (I assume you mean BINARY)?> need to setup a way to set a territory location status. > > We have two territories - LA and NY... > LA Territory - California Customers & Leads. > NY Territory - Customers & Leads in Connecticut, Maine, Massachusetts, > New Hampshire, New Jersey, New York, Pennsylvania, Rhode Island, > Vermont. > > I've got tables that store State, Area Codes and Zipcodes. I've got a > bit field setup to flag the 9 states, 33 Area Codes, and 7,564 Zipcodes > for these states. > > We don't always get complete data, so we have to check all of these > variables to see what area the customer or lead belongs to. > > I've tried using a UDF & passing in the State, Area, Zip of the > customer/lead and returning what territory the cust/lead belongs to. > > I've tried having a case statement. > > When I setup the UDF in the search query, our sql server came to a > screamin halt after the memery used by sql maxed out our server... it > jumped from only usin a few megs to just over two gigs, then our server > crashed... so I figured that UDF's are good but not for bulk-results... > > I've tried usin the case statement, but it increases the query time > from 4 seconds to 30 seconds. > > > I've tried checking if the area/zip codes matchup using "IN" and > "EXISTS" tags... > > I'm at a loss... seems like whatever I do, the queries go from quick to > crawl. > Any fresh idea's on how to grab this sorta data quickly would be much > appreciated. That's not the way to achieve great performance because you won't get the benefit of indexing if you have to search by manipulating bits. This is SQL not C++! I'd expect something more like this: SELECT ... FROM Customer AS C LEFT JOIN State AS S ON C.StateCode = S.StateCode LEFT JOIN Area AS A ON C.AreaCode = A.StateCode LEFT JOIN Zip AS Z ON C.ZipCode = Z.StateCode ; -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- Please post DDL and sample data. Make sure you include your indexes, and a
copy of the UDF, CASE, and EXISTS approaches. Also note how many rows of data you have in each table, and what version of SQL Server you are on. See the link below for an explanation: http://www.aspfaq.com/etiquette.asp?id=5006 One question though... Is your bit column used to indicate which territory a customer or lead is in? If not, what is it for? <TheDarkFrag***@gmail.com> wrote in message Show quote news:1156533577.230924.240380@b28g2000cwb.googlegroups.com... > ok, so I'm workin on a customer & leads part of this application. I > need to setup a way to set a territory location status. > > We have two territories - LA and NY... > LA Territory - California Customers & Leads. > NY Territory - Customers & Leads in Connecticut, Maine, Massachusetts, > New Hampshire, New Jersey, New York, Pennsylvania, Rhode Island, > Vermont. > > I've got tables that store State, Area Codes and Zipcodes. I've got a > bit field setup to flag the 9 states, 33 Area Codes, and 7,564 Zipcodes > for these states. > > We don't always get complete data, so we have to check all of these > variables to see what area the customer or lead belongs to. > > I've tried using a UDF & passing in the State, Area, Zip of the > customer/lead and returning what territory the cust/lead belongs to. > > I've tried having a case statement. > > When I setup the UDF in the search query, our sql server came to a > screamin halt after the memery used by sql maxed out our server... it > jumped from only usin a few megs to just over two gigs, then our server > crashed... so I figured that UDF's are good but not for bulk-results... > > I've tried usin the case statement, but it increases the query time > from 4 seconds to 30 seconds. > > > I've tried checking if the area/zip codes matchup using "IN" and > "EXISTS" tags... > > I'm at a loss... seems like whatever I do, the queries go from quick to > crawl. > Any fresh idea's on how to grab this sorta data quickly would be much > appreciated. > >> I've got tables that store State, Area Codes and Zipcodes. I've got a bit field [sic] setup to flag the 9 states, 33 Area Codes, and 7,564 Zipcodes for these states. << Do not program SQL as if it were a low-level language. Fields andcolumns are totally different concepts. It sounds like your tables should look more like this: CREATE TABLE Area_Codes (state_code CHAR(2)NOT NULL CHECK (state_code IN ('CA', 'NY', ..), area_code CHAR(3) NOT NULL CHECK (area_code IN ('212', ..), PRIMARY KEY (state_code, area_code) ); -- Using ranges instead of bits will make the data much smaller. CREATE TABLE ZipCodes (state_code CHAR(2)NOT NULL CHECK (territory_code IN ('CA', 'NY', ..), high_zipcode CHAR(5) NOT NULL UNIQUE CHECK (high_zipcode LIKE '[0-9][0-9][0-9][0-9][0-9]'), low_zipcode CHAR(5) NOT NULL UNIQUE CHECK (low_zipcode LIKE '[0-9][0-9][0-9][0-9][0-9]'), CHECK (low_zipcode <= high_zipcode) ); I am guessing that you do not need to match area codes to ZIP codes, since they can overlap. There are tools for that from mailing list software companies. You can now compute the Territory code with a CASE expression in a VIEW or query from the State codes. Another point is that SQ: is meant to do JOINs and not functions and procedures. Start thinking that way instead of in proceudral code. Please don't use bit flags. That is archaic, awkward, and just outright
cumbersome. Also as a design note, what happens when business is wonderful and the New York office has to split, and there are then two offices in New York City. Or business grows in the mid-south and there is a Louisiana (LA) territory. Territory abbreviations such as these can cause you a great deal of grief in the future. Point is: both NY and LA could be city or state. It is not a good idea to use ambiguous indicators to track data. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <TheDarkFrag***@gmail.com> wrote in message news:1156533577.230924.240380@b28g2000cwb.googlegroups.com... > ok, so I'm workin on a customer & leads part of this application. I > need to setup a way to set a territory location status. > > We have two territories - LA and NY... > LA Territory - California Customers & Leads. > NY Territory - Customers & Leads in Connecticut, Maine, Massachusetts, > New Hampshire, New Jersey, New York, Pennsylvania, Rhode Island, > Vermont. > > I've got tables that store State, Area Codes and Zipcodes. I've got a > bit field setup to flag the 9 states, 33 Area Codes, and 7,564 Zipcodes > for these states. > > We don't always get complete data, so we have to check all of these > variables to see what area the customer or lead belongs to. > > I've tried using a UDF & passing in the State, Area, Zip of the > customer/lead and returning what territory the cust/lead belongs to. > > I've tried having a case statement. > > When I setup the UDF in the search query, our sql server came to a > screamin halt after the memery used by sql maxed out our server... it > jumped from only usin a few megs to just over two gigs, then our server > crashed... so I figured that UDF's are good but not for bulk-results... > > I've tried usin the case statement, but it increases the query time > from 4 seconds to 30 seconds. > > > I've tried checking if the area/zip codes matchup using "IN" and > "EXISTS" tags... > > I'm at a loss... seems like whatever I do, the queries go from quick to > crawl. > Any fresh idea's on how to grab this sorta data quickly would be much > appreciated. > Wow, ok...
So If I setup these tables as they are in our current database in a stand-alone database, add some sample data, script the entire db & post it here... .... and post the code for the main select query, and the various types of Case and UDF's I've tried to use, that would help you help me? I could have that posted today/tomorrow. Side Note: I never actually went to school for this, sorta picked it up when I was a tester... people say I have a knack for it. I've learned by checking out the code used by various coders that I've met & worked with, and by going to sqlteam.com and a few other sql websites. Would you happen to know of a good resource to learn online, or even better, SQL courses in the Los Angeles area? Up until now, I thought I was doin good, but I see that I need to take the next step. um... also, I meant BIT (0,1) data type for ON/OFF, not an image
column... First, don't take anything here as gospel. You will get a lot of good
information here, from a lot of very bright people, many of whom are experts in the area of SQL and/or SQL Server. You will find a lot of wisdom regarding how to approach certain issues (i.e. bit flags), and a great deal of discussion/argument on these same topics. You need to read through the posts and determine on your own what is best. A book on relational database theory would be beneficial, although following this newsgroup will offer quite a bit of enlightenment also. There are countless books on SQL, relational database design, and SQL Server specifically, several written by frequent posters of this newsgroup. There are also many online resources which you will find simply by browsing this newsgroup and looking through today's posts. Second, you do not need to script the entire database, simply the relevant tables with their constraints, keys, and indexes, and just enough data that the query can be tested. Do not get discouraged because you have designed something contrary to how the majority of folks here would. You took a different approach, maybe made a mistake, and you will learn from it and move on. As Arnie quotes in his signature... Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <TheDarkFrag***@gmail.com> wrote in message Show quote news:1156877080.740784.42860@p79g2000cwp.googlegroups.com... > Wow, ok... > So If I setup these tables as they are in our current database in a > stand-alone database, add some sample data, script the entire db & post > it here... > > ... and post the code for the main select query, and the various types > of Case and UDF's I've tried to use, that would help you help me? I > could have that posted today/tomorrow. > > Side Note: I never actually went to school for this, sorta picked it up > when I was a tester... people say I have a knack for it. I've learned > by checking out the code used by various coders that I've met & worked > with, and by going to sqlteam.com and a few other sql websites. Would > you happen to know of a good resource to learn online, or even better, > SQL courses in the Los Angeles area? Up until now, I thought I was doin > good, but I see that I need to take the next step. >  |
|||||||||||||||||||||||
Other interesting topics