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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Logs get HUGE while indexing

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-01-26 : 14:46:36

i am creating an index on a table that is approx 220million rows.

the clustered index has only 5 columns in it, and yet the database log is already pushing 4GB and growing, the database is in simple recovery mode... is there anything else i can do to prevent so much logging while the table is indexing? i'm thinking it's the reason why indexing is taking so long...

prior to indexing this, the table was raw flat text.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-26 : 14:48:17
There is nothing that you can do to prevent so much logging. It needs to log it so that it can roll it back in case of an error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-01-26 : 16:02:57
any reason you can think of on why it takes so long to create an index? we're talking like 12+hrs to create the clustered index.

the drives are locally attached. (i've talked to you about this before as well, but that was when they were over a network san)

anything i can do to speed it up? maybe a hammer? large magnet?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-26 : 16:04:46
It's a resource issue, although 12+ hours is a very long time for a table of that size. 220 million rows isn't a small table, but it's not extra large either.

Anything interesting about the data types of the columns? How big is the max size row?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-01-26 : 16:18:57
the entire table si made up of varchar columns (mainly)

i do have a few however that are kinda wide that i dont use, maybe i should drop them? our vender sends fields that are "fill" fields (in otherwords data we dont get)

filler varchar(1),
filler2 varchar(1),
filler3 varchar(3),
filler4 varchar(4),
filler5 varchar(1),
filler6 varchar(5),
filler7 varchar(36),
filler8 varchar(140),
filler9 varchar(9),
filler10 varchar(6),
filler11 varchar(5),
filler12 varchar(71)


would it be more helpful if i posted the create script? thanks, i do appreciate the help.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-26 : 16:20:51
Right click on the database, go to reports, select the disk usage by top tables report. What does it say for the "Data (KB)" column for your table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-01-26 : 16:28:06
284,166,160KB
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-26 : 16:45:34
That's 270GB! I'd get rid of any columns or rows that you don't need in order to build that index faster.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-01-26 : 17:10:18
okay, next question, i have added a couple fields that are a combo of a few fields. for example, citystate which i update with city state when i get the data (ex: "phoenix az") for my .net app where our users can punch in all kinds of parameters to get counts online.

is there a better way to do that? maybe that'll clean up some space.
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-01-26 : 17:13:47
also, it's still gunna take a while even though in this case i'm only indexing 4 of columns? just curious.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-26 : 17:19:50
You should not add citystate to the table. Return city and state in different columns and then concatenate them from your .NET apps.

It doesn't matter that you are only indexing 4 columns as you are using a clustered index, which means it is the table itself. So you are actually creating a 270GB index!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-01-26 : 17:39:17
holy sweet mother! lol

okay as for the citystate, i did that, but the way i would select the data would be like:

where alltrim(city)+' '+state = 'phoenix az'

isnt that bad as far as indexing and speed because sql has to parse it or something? wwtd? (what would tkizer do :D )
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-26 : 17:48:48
Yes that is bad for performance. I would pass from the application two parameters, separating them into city and state. That way you can do this WHERE city = @var1 AND state = @var2. Don't force SQL to do work that the application should be doing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-01-26 : 18:01:33
yeah, but here's the thing our reps have the ability to select for example multiple cities in multiple states, so for example they can toggle boxes that will get them phoenix, az and orange, ca

how would you work that?

where
((city in ( 'phoenix' ) and state in ( 'az' ))
or (city in ( 'orange' ) and state in ( 'ca' ))

or u got a better idea? sorry, just picking your brain,
I haven't quite figured out the ins and the outs as for as query optimization.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-26 : 18:29:52
I'd still handle this via the aplication. Are you using stored procedure or inline sql?

I'm not a developer though, so I'm answering this from a DBA's perspective.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-01-26 : 18:37:34
stored procedure

here's the code for that part (currently using citystate)

IF @cityselect<>'DF' and @cityselect IS NOT NULL
   SELECT @sql = @sql + ' 
	OR o.citystate in ( ''' + REPLACE( @cityselect, N',', N''',''' ) + N''' )'


sampledata would be: "phoenix az,orange ca,glendale az,syracuse ny" (w/o the quotes)
i'm more worried about the query running fast, i dont mind having to write the code as long as i'm passing it to the database in the most efficent way possible. and a good method for this would help with other fields i use the same method for.

Thanks again.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-26 : 18:45:42
I'm going to defer to the people here with more SQL development experience to answer your question. I just know from a DBA perspective that the redundant data shouldn't be there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-01-26 : 18:57:02
DANG! :)

well, lets say it's just strait up text, and your writing your own query, would you do something like:

where
((city in ( 'phoenix' ) and state in ( 'az' ))
or (city in ( 'orange' ) and state in ( 'ca' ))

or a different way? i think i'm gunna write the sp to format it instead of asking sql to, save a little on the back end, just trying to get an idea how i would want it formated.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-01-26 : 19:07:03
If you are asking about table and query design, it would be almost impossible to give any advice without seeing the table design (CREATE TABLE statment) along with indexes, constraints, foreign keys, etc.

I will say that storing data like this is a recipe for disaster with a table that size:
"phoenix az,orange ca,glendale az,syracuse ny"
There is no way you will ever be able to create a usable index on that.





CODO ERGO SUM
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-01-26 : 20:46:25
well first off, i forgot to mention, an i know it's important, after i setup, import and index this table, it remains read-only until i get the next update from our vendor, which is quarterly.

here is the table. we get a flat text file from our vendor and this is the format the data is in, i then BCP import the data into the below table (i add the citystate,countystate,areacode and ltv for the .net front end program.

more or less, i'm trying to get an idea on how i can drop those fields and get a bit more performance out of it.

citystate is set to city + ' ' + state,
countystate is set to county + ' ' + state,
areacode is set to substring(phone,1,3),
and ltv is set to est_cur_home_val / mortgage amount


CREATE TABLE [dbo].[Consumer](
	[REC_TYPE] [varchar](1) NULL,
	[STREET] [varchar](30) NULL,
	[APT] [varchar](14) NULL,
	[CITY] [varchar](25) NULL,
	[STATE] [varchar](2) NULL,
	[ZIP] [varchar](5) NULL,
	[ZIP_PLUS_4] [varchar](4) NULL,
	[DELIVERY_POINT] [varchar](3) NULL,
	[CR] [varchar](4) NULL,
	[STATE_CODE] [varchar](2) NULL,
	[COUNTY_CODE] [varchar](3) NULL,
	[LATITUDE] [varchar](9) NULL,
	[FILLER] [varchar](1) NULL,
	[LONGITUDE] [varchar](9) NULL,
	[FILLER2] [varchar](1) NULL,
	[TIME_ZONE] [varchar](1) NULL,
	[PRESENCE_OF_CHILDREN] [varchar](1) NULL,
	[NUMBER_OF_PERSONS_IN_UNIT] [varchar](1) NULL,
	[PHONE] [varchar](10) NULL,
	[DO_NOT_CALL] [varchar](1) NULL,
	[NAME_TYPE] [varchar](1) NULL,
	[FIRST_NAME] [varchar](15) NULL,
	[MID_NAME] [varchar](1) NULL,
	[LAST_NAME] [varchar](20) NULL,
	[TITLE] [varchar](6) NULL,
	[SEX] [varchar](1) NULL,
	[YEAR] [varchar](4) NULL,
	[MONTH] [varchar](2) NULL,
	[DAY] [varchar](2) NULL,
	[EXACT_AGE] [varchar](2) NULL,
	[AGE_IND] [varchar](1) NULL,
	[HARD_AGE_IND] [varchar](1) NULL,
	[PREFIX] [varchar](2) NULL,
	[EDUCATION] [varchar](1) NULL,
	[MARITAL_STATUS] [varchar](1) NULL,
	[OCCUPATION] [varchar](2) NULL,
	[BUSINESS_OWNER] [varchar](1) NULL,
	[ETHNIC] [varchar](2) NULL,
	[ETHNIC_CONFIDENCE] [varchar](1) NULL,
	[ETHNIC_GROUP] [varchar](1) NULL,
	[LANGUAGE] [varchar](2) NULL,
	[RELIGION] [varchar](1) NULL,
	[COUNTRY_CODE] [varchar](2) NULL,
	[FILLER3] [varchar](2) NULL,
	[CENSUS_TRACT] [varchar](6) NULL,
	[CENSUS_BLOCK] [varchar](1) NULL,
	[SFDU_MFDU] [varchar](1) NULL,
	[ISPSA] [varchar](1) NULL,
	[INCOME] [varchar](1) NULL,
	[WEALTH_RATING] [varchar](1) NULL,
	[HOME_OWNER] [varchar](1) NULL,
	[LENGTH_RES] [varchar](2) NULL,
	[YEAR_BUILD] [varchar](4) NULL,
	[HOME_PURCHASE_PRICE] [varchar](4) NULL,
	[HOME_PURCHASE_DATE] [varchar](8) NULL,
	[TOTAL_HOME_VALUE] [varchar](4) NULL,
	[EST_CUR_HOME_VAL] [varchar](4) NULL,
	[HOME_HEAT_INDICATOR] [varchar](1) NULL,
	[POOL_INDICATOR] [varchar](1) NULL,
	[FIREPLACE] [varchar](1) NULL,
	[AIR_CONDITIONING] [varchar](1) NULL,
	[HOME_BUSINESS] [varchar](1) NULL,
	[CREDIT_CARD] [varchar](1) NULL,
	[CREDIT_GOLD_CARD] [varchar](1) NULL,
	[CREDIT_RATING] [varchar](1) NULL,
	[PC_OWNER] [varchar](1) NULL,
	[CHILD_AGE_0_3] [varchar](1) NULL,
	[CHILD_AGE_4_6] [varchar](1) NULL,
	[CHILD_AGE_7_9] [varchar](1) NULL,
	[CHILD_AGE_10_12] [varchar](1) NULL,
	[CHILD_AGE_13_18] [varchar](1) NULL,
	[BOOK_READER] [varchar](1) NULL,
	[MAIL_RESPONDER] [varchar](1) NULL,
	[MERCHANDISE_UPSCALE] [varchar](1) NULL,
	[MERCHANDISE_MALE] [varchar](1) NULL,
	[MERCHANDISE_FEMALE] [varchar](1) NULL,
	[MERCHANDISE_CRAFT] [varchar](1) NULL,
	[MERCHANDISE_GARDENING] [varchar](1) NULL,
	[MERCHANDISE_BOOK] [varchar](1) NULL,
	[MERCHANDISE_FOOD] [varchar](1) NULL,
	[MERCHANDISE_GIFT] [varchar](1) NULL,
	[MERCHANDISE_GENERAL] [varchar](1) NULL,
	[MAGAZINE_FAMILY] [varchar](1) NULL,
	[MAGAZINE_FEMALE] [varchar](1) NULL,
	[MAGAZINE_MALE] [varchar](1) NULL,
	[MAGAZINE_RELIGIOUS] [varchar](1) NULL,
	[MAGAZINE_GARDENING] [varchar](1) NULL,
	[MAGAZINE_CULINARY] [varchar](1) NULL,
	[MAGAZINE_HEALTH] [varchar](1) NULL,
	[MAGAZINE_DO_IT_YOUR_SEL] [varchar](1) NULL,
	[MAGAZINE_FINANCIAL] [varchar](1) NULL,
	[MAGAZINE_PHOTO] [varchar](1) NULL,
	[MAGAZINE_OPPORTUNITY] [varchar](1) NULL,
	[GARDNER] [varchar](1) NULL,
	[AUTO_BUFF] [varchar](1) NULL,
	[COOKING] [varchar](1) NULL,
	[DECORATING] [varchar](1) NULL,
	[DOGS] [varchar](1) NULL,
	[CATS] [varchar](1) NULL,
	[PET] [varchar](1) NULL,
	[TRAVELER] [varchar](1) NULL,
	[EXERCISE] [varchar](1) NULL,
	[OUTDOOR] [varchar](1) NULL,
	[OUTDOOR_SPORT] [varchar](1) NULL,
	[CONTRIBUTOR_RELIGIOUS] [varchar](1) NULL,
	[CONTRIBUTOR_POLITICAL] [varchar](1) NULL,
	[CONTRIBUTOR_HEALTH] [varchar](1) NULL,
	[CONTRIBUTOR_GENERAL] [varchar](1) NULL,
	[CONTRIBUT_ENVIRONMENTAL] [varchar](1) NULL,
	[CONTRIBUT_PHONE] [varchar](1) NULL,
	[CONTRIBUT_CHARITIES] [varchar](1) NULL,
	[INVESTMENT] [varchar](1) NULL,
	[MORTGAGE_LENDER] [varchar](25) NULL,
	[MORTGAGE_AMOUNT] [varchar](4) NULL,
	[MORTGAGE_RATE] [varchar](4) NULL,
	[MORTGAGE_RATE_TYPE] [varchar](1) NULL,
	[MORTGAGE_TERM_IN_MONTHS] [varchar](4) NULL,
	[MORTGAGE_LOAN_TYPE] [varchar](1) NULL,
	[MORTGAGE_TRANSACTION] [varchar](1) NULL,
	[HOME_PURCHASE_TYPE] [varchar](1) NULL,
	[REFINANCE_DATE] [varchar](8) NULL,
	[REFINANCE_AMOUNT] [varchar](4) NULL,
	[REFINANCE_LENDER] [varchar](25) NULL,
	[REFINANCE_RATE_TYPE] [varchar](1) NULL,
	[REFINANCE_TERM_IN_MONTH] [varchar](4) NULL,
	[REFINANCE_LOAN_TYPE] [varchar](1) NULL,
	[HOUSEHOLD_SEQ_NUM] [varchar](1) NULL,
	[MOSAIC_NAME] [varchar](3) NULL,
	[MOSAIC_GLOBAL] [varchar](1) NULL,
	[CASS_CODE] [varchar](1) NULL,
	[DPV_CODE] [varchar](1) NULL,
	[ADDR_TYPE_CODE] [varchar](1) NULL,
	[DPV2_CODE] [varchar](1) NULL,
	[FILLER4] [varchar](4) NULL,
	[FILLER5] [varchar](1) NULL,
	[No_Addr_Flag] [varchar](1) NULL,
	[MFDU_5plus_Units] [varchar](1) NULL,
	[Suspect_Deliverabilty_Fl] [varchar](1) NULL,
	[FILLER6] [varchar](5) NULL,
	[Full_Name] [varchar](30) NULL,
	[HH_ID#] [varchar](9) NULL,
	[Invd_ID#] [varchar](5) NULL,
	[FILLER7] [varchar](36) NULL,
	[LACS_Status_Indicator] [varchar](1) NULL,
	[FILLER8] [varchar](140) NULL,
	[Overall_Match_Level] [varchar](1) NULL,
	[Phone_Conf_Code] [varchar](1) NULL,
	[Time_Zone_Letter] [varchar](1) NULL,
	[FILLER9] [varchar](9) NULL,
	[DMA_Suppress] [varchar](1) NULL,
	[FILLER10] [varchar](6) NULL,
	[County_Name] [varchar](25) NULL,
	[FILLER11] [varchar](5) NULL,
	[Perm_ID] [varchar](9) NULL,
	[FILLER12] [varchar](71) NULL,
	[Email_ID_Number] [varchar](20) NULL,
	[EOR] [varchar](1) NULL,
	[areacode] [varchar](3) NULL,
	[countystate] [varchar](35) NULL,
	[citystate] [varchar](30) NULL,
	[ltv] [float] NULL,
	[uid] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site