Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2007-04-26 : 17:38:57
|
| okay, so i've been slowly working on an application mostly VB/ASP.Net based that is basically i dummied down query builder. the user selects a bunch of controls off the website, then submits it for a query. the server then executes a dynamic stored procedure, and the website builds a count with the results. i have a couple of questions, i've come to the point where if the query isnt finished inside 30 seconds, the site times out, so i'm looking at a query here, and i have a "BookMark LOOKUP" that is costing me 32% of the query. what can i do to stop that?how can i get my indexes to perform faster, and is it an issue with the procedure that causes some queries to time out, or is it just the way the data is organized? |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-26 : 17:44:53
|
| Bookmark is the most expensive form of look up SQL Server has to do in order to retrieve the data you are requesting. It means the data is not available on any index pages so it has to go to the actual data page and look up the value. adding indexes on the columns or even addint the column to an existing index as a covering index can help. Post the query you have, your table structure and indexes on the table so we can suggest how you can avoid the bookmark lookup.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-04-26 : 17:56:38
|
table: CREATE TABLE [dbo].[condnc] ( [fips_state] [char] (2) , [state_abbrv] [char] (2) , [zip] [char] (5) , [zip_four] [char] (4) , [del_point] [char] (3) , [car_rte] [char] (4) , [city_abbrv] [varchar] (13) , [city] [varchar] (28) , [addr_house_num] [varchar] (10) , [addr_pre_dir] [varchar] (2) , [addr_st_name] [varchar] (28) , [addr_st_suff] [varchar] (4) , [addr_post_dir] [varchar] (2) , [addr_unit_des] [varchar] (6) , [addr_unit_desnum] [varchar] (8) , [address1] [varchar] (30) , [fips_cnty] [char] (3) , [county_name] [varchar] (25) , [census_tract] [char] (6) , [census_block] [char] (1) , [lattitude] [char] (9) , [longitude] [char] (10) , [fips_ispsa] [char] (5) , [wealth_rating] [char] (1) , [time_zone] [char] (1) , [phone] [char] (10) , [homeowner] [char] (1) , [est_inc] [char] (1) , [per1_fname] [varchar] (15) , [per1_mi] [char] (1) , [per1_lname] [varchar] (20) , [per1_title] [varchar] (6) , [per1_gender] [char] (1) , [per1_dob] [char] (8) , [per1_ageconf] [char] (1) , [per1_age] [char] (2) , [per1_msconf] [char] (1) , [per1_ms] [char] (1) , [per2_fname] [char] (15) , [per2_mi] [char] (1) , [per2_lname] [varchar] (20) , [per2_title] [varchar] (6) , [per2_gender] [char] (1) , [per2_dob] [char] (8) , [per2_ageconf] [char] (1) , [per2_age] [char] (2) , [child_pres] [char] (1) , [child_0_3] [char] (1) , [child_0_3_gender] [char] (1) , [child_4_6] [char] (1) , [child_4_6_gender] [char] (1) , [child_7_9] [char] (1) , [child_7_9_gender] [char] (1) , [child_10_12] [char] (1) , [child_10_12_gender] [char] (1) , [child_13_18] [char] (1) , [child_13_18_gender] [char] (1) , [religious_contrib] [char] (1) , [political_contrib] [char] (1) , [health_contrib] [char] (1) , [general_contrib] [char] (1) , [hm_purprice] [char] (8) , [hm_purdate] [char] (8) , [hm_year_build] [char] (4) , [donate_env] [char] (1) , [char_contrib] [char] (1) , [pres_cc] [char] (1) , [pres_perm_cc] [char] (1) , [oo_mtg_amnt] [char] (8) , [oo_mtg_lender_name] [varchar] (25) , [oo_mtg_rate] [char] (4) , [oo_mtg_rate_t] [char] (1) , [oo_mtg_loan_t] [char] (1) , [dnc] [char] (1) , [oo_refi_deed_date] [char] (8) , [oo_refi_amnt] [char] (4) , [oo_refi_lender_name] [varchar] (25) , [oo_refi_rate_t] [char] (1) , [oo_refi_month_term] [char] (4) , [oo_refi_loan_t] [char] (1) , [citystate] [varchar] (30) , [countystate] [varchar] (30) , [areacode] [varchar] (3) ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [condnc11] ON [dbo].[condnc]([zip], [child_pres]) ON [PRIMARY]GO CREATE INDEX [condnc1] ON [dbo].[condnc]([child_pres], [state_abbrv], [address1], [wealth_rating], [homeowner], [est_inc], [per1_age], [per1_ms], [hm_purprice], [hm_purdate], [hm_year_build], [pres_cc], [oo_mtg_amnt], [oo_mtg_lender_name], [oo_mtg_rate_t], [oo_mtg_loan_t]) ON [PRIMARY]GO CREATE INDEX [condnc3] ON [dbo].[condnc]([areacode]) ON [PRIMARY]GO CREATE INDEX [condnc7] ON [dbo].[condnc]([state_abbrv], [wealth_rating], [homeowner], [est_inc], [per1_age], [county_name]) ON [PRIMARY]GO CREATE INDEX [condnc77] ON [dbo].[condnc]([state_abbrv], [wealth_rating], [homeowner], [est_inc], [per1_age], [county_name], [per1_lname], [per1_ms]) ON [PRIMARY]GO query: [SELECT 'Con' as source,o.per1_fname,o.per1_lname,rtrim(o.per1_fname)+' '+rtrim(o.per1_lname) as name, rtrim(o.address1)+' '+rtrim(o.addr_unit_des)+' '+rtrim(o.addr_unit_desnum),o.city, o.state_abbrv,o.zip,o.phone,o.oo_mtg_amnt,o.oo_mtg_lender_name, o.hm_purdate,o.per1_age,o.wealth_rating,o.est_inc,o.hm_purprice,o.county_name,o.oo_mtg_rate_t,o.oo_mtg_loan_tFROM dbo.condnc o o.per1_age >= 25AND o.per1_age <= 65AND o.est_inc >= 'E'AND o.per1_ms = 'M'and o.pres_cc='y' and (o.countystate in ( 'MIAMI-DADE FL' ))[/code]so it's basically because i'm selecting a field that's not part of any indexes? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-26 : 18:07:52
|
| Looks like you have duplicate indexes condnc7 and condnc77. When you have a covering index only the left most column in the column list matters most. So among your indexes condnc7 and condnc77 have same left most column. You can change one of them to change the order and bring in some other column that is used in the WHERE condition. Depending on your data distribution having as many indexes on as many columns you have in your WHERE clause helps. If you are SELECTing columns that are not covered in the Indexes, SQL Server has to do a BOOKMARK LOOKUP to get those values. Sometimes it cannot be avoided but can be reduced as much as possible. consider adding separate non clustered indexes on per1_age, est_inc, per1_ms , pres_cc and countystate. Blindly adding an index on each of these also does not help. look at the data distribution in the column For ecample consider column est_inc. indexing columns that have very few distinct values doesnt help (example a flag column or a status column or a Gender column etc).************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-26 : 18:14:05
|
| You can also get rid of the bookmark lookup if you change the clustered index. Are you sure you want the clustered index on zip and child_pres? Where is your primary key constraint?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-04-26 : 18:15:15
|
| question, do you think i should just maybe build one index, that would have every field i would select by? it'd be about 20ish fields.from what you've explained, it seems that countystate is the field that it's probably doing the bookmark lookup on wouldnt you agree? perhaps i should add it to the clustered index? the fields you mentioned i should maybe add indexes for all except countystate have a low selectability (like less then 10 choices for est_inc, and the others are 3.) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-26 : 18:21:40
|
| No! That will significantly impact your DML operations. Can you post the text of the execution plan?You definitely need an index on countystate. Those other columns look like ones that would have very low selectivity in them.What do you usually sort on in this table?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-04-26 : 18:28:20
|
| i dont really have anything for a primary key. there isnt anything in the table that is UNIQUE i know that's large SQL no-no but it's just how i get the data from our vender, and i dont really link anything up to it. as for the zip/childpres, not i'm not sure if i want it in there. i came up with most of these indexes via index tuning wizard.what's a DML Operation :o) and what is the key combo to convert the execution to text? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-04-26 : 19:55:28
|
| aahhhh now i have to read :o) alright, i'll start reading the link, but anything else you guys could recommend in the mean time?another question. i read somewhere that with indexing, you can have your data on one hard disk, and the indexes on another. is this true? if so is it much of a performance increase? also, i have one raid set on 4, 160gb 10,000 RPM Raptor drives, and have another 4, 500gb 7200 Caviar drives. which raid set would be better for data, which for indexing? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-26 : 20:03:14
|
| What you probably read was that indexes and data on different file groups on different drives can give better peformance. Clustered index should be created on the same filegroup as the actual table but non-clustered indexes can be created on a filegroup residing on a different drive. this can sometimes boost performance depending on your queries as SQL can get the information it needs from the index pages (which are smaller than the data pages). Having the right indexes is more important than separating the filegroups.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-04-26 : 20:06:44
|
| so after i index, and normalize my data, THEN it might be a benefit, but probably not until then? that's kinda what i figured...so after i get everything how i want it, can i set the indexes up in a different filegroup? or is it a pain to do that? just curious becuase it'd be a bummer to create everything, get it all how i want, then have to tear it all down and re-arrange it to get a LITTLE more umph. know what i mean? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-26 : 20:09:26
|
| albertkohl,Could you put a hard return in your post where you put the code for us to view? The hard return should be put somewhere in the index named condnc1. This will help with the scrolling issue we have with this thread. It is very annoying.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-04-26 : 22:42:50
|
| sorry about that :\ so how do i post the text version of the execution plan? |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-04-26 : 23:26:30
|
| Okay, so i TRIED to read that page.... and WOW my head hurts. i assume that not all databases can achive ALL levels for normalization is that correct?also, correct me if i'm wrong, but it would seem that the first step i would want to take, is add a unique field to my database, a field i'll call UID. after that, i was thinking about spliting the master table into groups like so:Master Table [uid] [int] IDENTITY (1, 1) NOT NULL , [per1_fname] [varchar] (15) , [per1_lname] [varchar] (20) , [address1] [varchar] (30) , [city] [varchar] (28) , [state_abbrv] [char] (2) , [zip] [char] (5) , [phone] [char] (10)Geo Table [uid] [int] IDENTITY (1, 1) NOT NULL , [citystate] [varchar] (30) , [countystate] [varchar] (30) , [areacode] [varchar] (3) , [fips_state] [char] (2) , [zip_four] [char] (4) , [del_point] [char] (3) , [car_rte] [char] (4) , [city_abbrv] [varchar] (13) , [addr_house_num] [varchar] (10) , [addr_pre_dir] [varchar] (2) , [addr_st_name] [varchar] (28) , [addr_st_suff] [varchar] (4) , [addr_post_dir] [varchar] (2) , [addr_unit_des] [varchar] (6) , [addr_unit_desnum] [varchar] (8) , [fips_cnty] [char] (3) , [county_name] [varchar] (25) , [census_tract] [char] (6) , [census_block] [char] (1) , [lattitude] [char] (9) , [longitude] [char] (10) , [fips_ispsa] [char] (5) Person2 table [uid] [int] IDENTITY (1, 1) NOT NULL , [fips_state] [char] (2) , [zip_four] [char] (4) , [del_point] [char] (3) , [car_rte] [char] (4) , [city_abbrv] [varchar] (13) , [addr_house_num] [varchar] (10) , [addr_pre_dir] [varchar] (2) , [addr_st_name] [varchar] (28) , [addr_st_suff] [varchar] (4) , [addr_post_dir] [varchar] (2) , [addr_unit_des] [varchar] (6) , [addr_unit_desnum] [varchar] (8) , [fips_cnty] [char] (3) , [county_name] [varchar] (25) , [census_tract] [char] (6) , [census_block] [char] (1) , [lattitude] [char] (9) , [longitude] [char] (10) , [fips_ispsa] [char] (5) Mortgage table [uid] [int] IDENTITY (1, 1) NOT NULL , [oo_mtg_amnt] [char] (8) , [oo_mtg_lender_name] [varchar] (25) , [oo_mtg_rate] [char] (4) , [oo_mtg_rate_t] [char] (1) , [oo_mtg_loan_t] [char] (1) , [oo_refi_deed_date] [char] (8) , [oo_refi_amnt] [char] (4) , [oo_refi_lender_name] [varchar] (25) , [oo_refi_rate_t] [char] (1) , [oo_refi_month_term] [char] (4) , [oo_refi_loan_t] [char] (1) Homeowner Table [uid] [int] IDENTITY (1, 1) NOT NULL , [wealth_rating] [char] (1) , [hm_purprice] [char] (8) , [hm_purdate] [char] (8) , [hm_year_build] [char] (4) , [homeowner] [char] (1) Demograph table [uid] [int] IDENTITY (1, 1) NOT NULL , [pres_cc] [char] (1) , [pres_perm_cc] [char] (1) , [est_inc] [char] (1) , [per1_age] [char] (2) , [per1_ms] [char] (1) , [child_pres] [char] (1) ,Misc table [uid] [int] IDENTITY (1, 1) NOT NULL , [time_zone] [char] (1) , [per1_mi] [char] (1) , [per1_title] [varchar] (6) , [per1_gender] [char] (1) , [per1_dob] [char] (8) , [per1_ageconf] [char] (1) , [per1_msconf] [char] (1) , [child_0_3] [char] (1) , [child_0_3_gender] [char] (1) , [child_4_6] [char] (1) , [child_4_6_gender] [char] (1) , [child_7_9] [char] (1) , [child_7_9_gender] [char] (1) , [child_10_12] [char] (1) , [child_10_12_gender] [char] (1) , [child_13_18] [char] (1) , [child_13_18_gender] [char] (1) , [religious_contrib] [char] (1) , [political_contrib] [char] (1) , [health_contrib] [char] (1) , [general_contrib] [char] (1) , [donate_env] [char] (1) , [char_contrib] [char] (1) , [dnc] [char] (1) Am I WAY off base here? or am i on the right track?again, my main goal here is PURE performance. i want basically any query i run to return within 30 secondsthese tables would be about 135 Million records so... |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-27 : 00:18:49
|
| (1) whats with the naming convention? Personally I dont like underscores in table names or column names. HEre's a sample: http://databases.aspfaq.com/database/what-naming-convention-should-i-use-in-my-database.html(2)Also, I dont see any PK-FK relation? You have a master table but how is it linked to the child tables? None of your tables have PK.(3) Try to avoid CHAR(x) if the length of the value could be different. (4) Why are prices/amounts/dates in CHAR?************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-04-27 : 00:29:57
|
| (1)The naming convention is something that is handed down from the vender we get the data from (we get it in raw text though) and they have a tendency to change the field order, so import is a pain in the butt if change it, nothing likes to line up and it costs me about 2 hours setting everything up. we've been using these field names for about 2 years now, so i'm used to it anyway. (2)i havnt got to the point to where i know how to set up PK-FK relations would love some help though. do i basically set the UID to primary key on all the tables? do i really even need and F.K.?(3)i use Char only on fields i KNOW the value will be exactly how many characters i define, learned that the hard way a number of months ago. (4)dates are formatted yyyymmdd and and prices are always ### so again, i just kinda kept what was handed down. do you recommend i change that?also, i really do appreciate the time your putting in on this to assist me. thank you in advance very much. ALL OF YOU! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-27 : 00:41:55
|
| (1) ok.(2) Yes, a 'good' table has a PK. You can set up the UID as PK. Now what is the column you are gonna use to link the Master table with the child tables? (3) ok. good you know your data.(3) Yes, dates should be of type DateTime and amounts should be Decimal(x,x). Makes it easy for querying. and makes sense to store the data in the format they are meant to be.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-04-27 : 00:47:33
|
| (1) GREAT!(2) okay, i was planning on using the UID to link everything is that good?(3) i've been doing nothing but Manual queries on it for the last 2 years... i would HOPE so :o)(4) okay, but is it easy to convert? is it as simple as changing the field to that data type, and SQL is smart enough to convert it properly? also, for amounts, we dont have any .00 (the decimal part) should i maybe use Int? or still use dec and set the places to 0 (atleast that's what i'd do in FoxSLOW a.k.a. foxpro |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-27 : 00:53:32
|
| how are you doing the import? DTS? BCP? SSIS? or some custom tool? In DTS wizard you can change the datatypes on one of the screens in the wizard.If you change the UID PK it should be fine, I think. Now to use tha to link the tables, it depends on how you are loading the data. Are you doing it in stages, one table after another? or does the data get loaded independent of each table? The UID, currently is an IDENTITY column meaning SQL assigns a number to each row in the order in which data is received. So there would be no way to match the UID of one table to another. IF the UIDs match it would be a coincidence. You have to think through this.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-04-27 : 01:02:16
|
| i use a DTS import. i get one file with EVERYTHING in it. it's basically the table i first posted. i know DTS prompts for data types, but wont it try to convert 20070426 into a date format? if so, will there be an issue? my basic plan atleast i thought, would be load the data into one LARGE table, with all the fields. add a UID field and let SQL assign the UID. after that, i would basically select (fields) into (table) from (mainTable) repeat this a couple times until i have all the child tables laid out.no? |
|
|
|
Next Page
|
|
|
|
|
What is a bookmark, and why is it costing me 32%?