| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2010-07-09 : 13:29:46
|
| is there a way to setup a table so that selecting data off it, doesnt cause locks when another user is inserting into it?i basically have a table that stores session variables for a website, every time the user toggles an option, it stores that value into a table, but the values have to be selected @ the same time by other users... any times the selects take more than a few seconds the site starts tripping...any thoughts would be helpful, thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-09 : 14:26:02
|
| worked PERFECTLY! really appreciate it... now question, this is a table of under 1 million rows, what kind of overhead can i expect from changing this...? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-09 : 15:41:37
|
| Well you have to understand the difference between READ_COMMITTED and READ_COMMITTED_SNAPSHOT. If you are okay with getting a snapshot of the data that can change while you are reading it, then READ_COMMITTED_SNAPSHOT would be good. You don't get a dirty read with it, but you need to understand the difference. A banking system would need to use READ_COMMITTED, plus ecommerce sites. READ_COMMITTED_SNAPSHOT is perfect for the systems that I work on.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-07-09 : 16:05:14
|
quote:
Originally posted by tkizerI don't ever use it in production code.Tara Kizer
If I'm gonna use it in PROD, there is NO Sense in using it ANY other way...noobs get used to shortcuts too easily'Nough saidIn a nutshell that is my prob w/ M$, where IBM is painful but Direct..there is only 1 right way..being German...I tend to believe that...it's in my genesBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-20 : 18:48:18
|
| Tara, it seems like two different insert commands cannot run at the same time, one will block the other... is this normal behavior? and if so, is there anything (aside from waiting) around it? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-21 : 02:39:25
|
the data is partitioned accross 4 drives, 8 files...CREATE PARTITION FUNCTION [pf_Supp](varchar(5)) AS RANGE RIGHT FOR VALUES (N'0', N'1', N'24067', N'38614', N'60131', N'78259', N'95133')CREATE PARTITION SCHEME [ps_Supp] AS PARTITION [pf_Supp] TO ([PRIMARY], [FileGroup2], [FileGroup3], [FileGroup4], [FileGroup5], [FileGroup6], [FileGroup7], [FileGroup8])GOCREATE TABLE [dbo].[Suppression]( [LN] [varchar](30) default '', [Addr] [varchar](50) default '', [zip] [varchar](5) default '', [phone] [varchar](10) default '', [CustomerID] [uniqueidentifier] NOT NULL, [Date_Added] [date] default getdate(), [IsPerm] [char](1) default '' on ps_supp (zip))GOCREATE NONCLUSTERED INDEX [ix_dboSupp_001] ON [dbo].[Suppression] ( [LN] ASC, [phone] ASC, [CustomerID] ASC, [Date_Added] ASC)GOCREATE NONCLUSTERED INDEX [ix_dboSupp_002]ON [dbo].[Suppression] ([CustomerID])GO when i'm doing inserts it's kinda like this one: insert into Suppression select LEFT(LN,30), LEFT(addr,50), LEFT(Zip,5), LEFT(phone,10), CustomerID, Date_Added, IsPerm from supp.dbo.SuppImport this table was 2,016,651 records, and it took 32 minutes to insert the data... also, lets say this insert is going on, and i had another 10k insert i needed to run off another table intosupp.dbo.suppression, that process would be blocked by the insert running above until it has completed.anything else you need? Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-22 : 00:01:41
|
| only thing is, it can be slightly different on every query... can u bulk-load using t-sql? or no, just packages? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-22 : 00:47:16
|
| what i ment was can i use normal t-sql to do the bulk-load... rather then an SSIS package.is there a way to pass parameters to an ssis package from either command-line, or though SSIS it self? if i was able to call the package, and give it a hand-ful of parameters, that might work. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-22 : 01:01:30
|
| thanks for pointing me in the right direction. really appreciate it. |
|
|
|
|
locking issue
Wait until you get into the billions!The only overhead is the tempdb utilization, so watch disk space in tempdb. Make sure your tempdb is optimized. You should have one tempdb data file per CPU (per core, not per socket). You should also have tempdb on its own set of disks.Tara Kizer