To Divide One Long Db Table Into Many Shorter Ones???
Jun 8, 2001
Hello Folks,
I'm looking for the group's collective wisdom on the following issue, I'm sure many have been confronted with it.
I'm designing a db for a website that will utilize SQL Server 7. I have few tables that I think will grow very large row wise and that will be written to an read from frequently. I sense that I might be able to get better perfomance out of the system if I split many of these large tables up (row wise) into many smaller tables.
For example, The website that I'm working on has a "MailingAddresses" db table that contains the mailing addresses for the sites users (subscribers and other misc users - 1 record per user, anticipating 70,000+ users). Each user can update their record, and there will be frequent queries to the tables to get addresses for both internal admin use and for display on public webpages.
The website has five distinct sections that are in some sense like five distinct websites. Each user belongs to only one of these sections and they won't migrate between sections. Therefore I'm considering breaking up the one large "MailingAddresses" tables into five smaller tables, one for each section, i.e., "MAddressesSectionA", ..., "MAddressesSectionE".
These tables will have the same fields and constraints. Also of course there would be the same number of reads and writes in total with the five smaller tables as compared to that for the one big table. Also the combined size of the info in the five smaller tables would be the same as that for the one large table.
Though it's going to more of a pain to manage five tables versus one, I have a hunch it might be easier for the dbms to handle reads and writes with five smaller tables than with one large table...
...Of course this is true when queries are mainly respective to users from just one or two of the sections (as might be the case) - the big table then has the overhead of the address records for users in the other sections. BUT is there any benefit with the five smaller tables route when they are all frequently accessed??? Sure each select query has fewer records to go through, but with all five tables in play the dbms has to deal on average with the same amount of info as in the one big table.
What do you folks think, to divide or not to divide the big table(s) up row wise into smaller tables?
I guess the issue is summed up in this question: In general, can a dbms better handle in memory, and more quickly write to and query access - one BIG table with a+b+...+n records, or N smaller tables with a, b,...,n records respectively?
Entity Value A 2424053.500000 B 1151425.412500 C 484810.700000 Table 2 contains
Entity Formula A (2100*(1-0.0668)*24*mday*10) B (1000*(1-0.0575)*24*mday*10) C (1260*(1-0.09)*24*mday*10)
Where mday is number of days taken from user
I need to calculate the output of value/formula for each entity can you provide me the query for the same
The datatype for formula column is varchar
I do not have the liberty to use cursors or loops.mday will be a input fromt the user say 'mday = 31' ..i need to divide the value in the first table with the computed value of the formula after replacement
I have a data table from a old system. There are 10 data fields stored in one row at this table. How can I seperate those fields into another table to be 10 rows? Any component in Data Flow can I use?(I have tried several component...) by the way, the original table is a large table, contain 3,000,000 rows.
I have a T-SQL query that is used to pull up some data for once-a-day export, just out of curiosity more then anything, is there a way to make this shorter?SELECT DISTINCT u.userId,u.lastName,u.firstName,u.address1,u.address2,u.city,u.state,u.zip,CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)= 0 THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)<> 0 THEN 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) <> 0THEN 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0))WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) <> 0THEN 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0))END CPGkeyFROM [...] JOIN [...]I'm hoping there is something in a way of....If userClass Between 1 and 3 ThenIF COALESCE(u.RetailerNumber_fk,0)= 0 Then 'Corporate'ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))End IF as CPGKey,Thanks in advace.
I need query like this that make a row of numbers (in one execute)
SELECT a from ( SELECT -1 a UNION ALL SELECT -2 UNION ALL SELECT -3 UNION ALL SELECT -4 UNION ALL SELECT -5 UNION ALL SELECT -6 UNION ALL SELECT -7 UNION ALL SELECT -8 UNION ALL SELECT -9) t;
I have a table to which data is being imported via bcp. When I get this data there are some fields nvarchar that has some decimal, for instance 1098.000000 is always 6 zeros..What I want to achieve is to make the above number only 1098
So what I did was this: LEFT (AMOUNT, LEN(AMOUNT)-7) AS AMOUNT
But i get an error saying invalid lenght parameter passed to the left or substring
Today I ran a query from my ASP page that returned with an error message stating my command had timed out. Fine. I searched the forums (thank god for the internet), and added cmd.CommandTimeout = 120. Great! That solved my problem.
My question is that from everything I read, the command timeout is set to 30 seconds by default. Yet my query runs in 5 seconds or so with the property set, but wouldn't run at all without it. Is it possible my command timeout was set somewhere else to less than 30 seconds? It's almost as if the server THOUGHT it would take longer than 30 seconds, and gave up, before actually running the query (which has all needed indexes to make the query fast). I just don't understand why I was getting this error for what turns out to be a 5 second query. When I shorted the query to 4 seconds or so (reducing the date range for the report), it ran fine in 4 seconds without the timeout error. So basically, going to 5 seconds or more caused this error. Where are the other places that the CommandTimeout property might be set? On the SQL Express database itself? I didn't see any ASP.NET config properties addressing it. Is there somewhere else I can look?
Exception ex.Message I am getting in debug mode calling AcquireConnection is much shorter than the logged message in run time because all the errors come to output window in debug. Is there any way to get all these errors description from exception object in debug time to be able to parse it? Thanks.
HiThere is an application that runs on sql server.The application selects/updates some few tables frequentlyOnce there is even a select on this table .It blocks other userssometimes for very long.Is there anything that can be done to reduce this?The table has 18000 rows and does not seem to have an indexI thought indexing might help but 18000 rows without an index isno reason for 30 minutes of lock time.I will appreciate your help as usualVince
I have written a stored procedure to list out all tables in whichrows or the table itself is locked. The only information I amnot able to get is the time when the lock occurred. The way Iwant is that if I run the procedure it should show all lockson a table which are at least 5 or x seconds old. This way I canavoid momentary locks on a table which go away after few seconds.Which table and column of master database has that information?Thanks.--email id is bogus
I have a long query which I have set off and would like to stop, andrename one of the tables used.My query is due to my lack of understanding of the underlyingstructure of MSSQL-Server...So say and update updates TABLE_A and I stop it, whilst thistransaction is rolling back I attempt to rename TABLE_A to TABLE_A_OLDand rename a different table to become TABLE_A. I am assuming thatthe rollback actions will use the object reference of TABLE_A_OLD andcontinue to rollback the effects on the correct table and not corrupt'new' TABLE_A... or will it not allow me to rename TABLE_A until therollback is complete?Thanks for any help!Steve
I've got an excel file that I want to import into a database table. The longest text in a cell is 385 characters. I've made the fields in the table nvarchar(1024).
I created a data flow task for the import.
When I run this task, I get the following error:
[Excel Source [1]] Error: There was an error with output column "Line Text" (52) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". [Excel Source [1]] Error: The "output column "Line Text" (52)" failed because truncation occurred, and the truncation row disposition on "output column "Line Text" (52)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
is it possible that there is a restriction on the length of the text ?
We have a web-based third-party application that has both background processes and user activity requests running in the same database (SQL Server 2005 SP2). The problem is that a background process will start a long-running transaction and hold an exclusive lock on a few rows in a given table (a small table, <100 rows). The web clients need to scan this same table, but when their "select *" statements get to those locked row(s), the web client queries stall waiting for that exclusive lock to be released. This effectively brings the entire web front end to a halt because all clients must hit this table for each user action. I realize that this is the classic lock condition that multiversioning databases like Oracle, PostgreSQL, SQL Server Compact Edition, and other databases do not suffer because they don't use shared read locks like SQL Server. But since we're on SQL Server for this app, what is the way to get around this problem? Modifying the clients to use WITH (NOLOCK) is not an option... there will be major consistency issues unless the clients run in Read Committed or higher. Any ideas? We could tweak this app if needed. Does SQL Server 2008 introduce multiversioning or at least some mechanism to get around this problem? I did not see it mentioned on the Microsoft site, but maybe I missed it. Thanks in advance.
The scenario is the data comes from various sources and its staged into staging database. From this staging database it goes into data warehouse database. Everyday this staging database is truncated and repopulated from various sources. I've a dimension table called DimCustomers which consists of around 300,000 rows and has lots of different types of SCD columns. It takes around 4-5 hours to load data from staging to this dimension table. Currently I'm using a For Loop container which uses a store proc to extract 15000 rows each time and populate my dimension tables. First couple of loops it goes off quickly but as and when the number reaches half of the count it slows down and hence it takes around 4-5 hours to load data.
What would be the best approach to populate this kind of dimension table.
i need to full-text index a table so that i can easily search the text fields of that table.. the table has about 21,000 rows, and i was wondering how long it might take to full-text index it?
We have a proc that adds some fields to a few tables of ours and normally there are no issues. For one of our client databases this process is taking anywhere from 5-10 minutes to add the fields. This causes an issue where the app will timeout waiting. After plugging around and looking at the proc and trying different items i found it to only be for this one database and ONLY when there is data in the table. If i truncate the table and run the same procedure everything is fine. Tables all have same index on 4 columns and the columns being added are not indexed because of the stupid hoops we have to jump thru to pre-pivot data for our reporting package.
I have several reports that are looking for a code within a certain set of codes or ranges. The specific list of codes to be including is determined by the end user. Currently my "IN" statement can be a hundred lines, listing several ranges, lists of specific codes, etc. I am constantly getting asked what codes does it include, is this code included, etc. Sometimes they'll give me a printed 10 page list of codes and want me to compare to what I have included in the report. Not ideal in the slightest.
What I'd like to do is have a table or a file of some kind somewhere where the end user can view the codes contained, add new ones, and delete ones they no longer want. Then I'd like to be able to just reference that file in my IN statement. Leaving the responsibility of listing the correct codes on them.
I have a field that divides one field by the sum of two others. However, when both of the two latter fields are 0's, I get a divide by zero error and the output halts. How do I get this so that for these records I put a NULL or UNDEFINED or something in there at least so I can see the rest of the output. Thanks.
I have this sql posted below which sometimes gets a divide by zero error. How can you get around this error please
SELECT COUNT(DISTINCT dbo.safety_obs_data.form_id) AS TotalObs, SUM(dbo.safety_obs_data.safe) AS TotalSafe, SUM(dbo.safety_obs_data.unsafe) AS TotalUnsafe, CONVERT(decimal(18, 2), (SUM(CASE WHEN (safe) * 100.0) / (SUM(safe) + SUM(unsafe)))AS [% Safe], SUM(CASE WHEN unobserved = 1 THEN 1 ELSE NULL END) AS NotSeen, SUM(CASE WHEN made_safe = 1 THEN 1 ELSE NULL END) AS TotalMadeSafe, SUM(CASE WHEN sap_note = 1 THEN 1 ELSE NULL END) AS TotalSAPNote, SUM(CASE WHEN honk = 1 THEN 1 ELSE NULL END) AS TotalHonks, dbo.Employee.emp_user_id FROM dbo.safety_obs_data INNER JOIN dbo.Employee ON dbo.safety_obs_data.create_by_emp_no = dbo.Employee.emp_no WHERE (dbo.safety_obs_data.create_dte BETWEEN CONVERT(DATETIME, @start, 102) AND CONVERT(DATETIME, @end, 102)) GROUP BY dbo.Employee.emp_user_id
SELECT CASE WHEN (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Rspec_Vulgar = 'N' THEN 1 ELSE 0 END)) = 0 THEN '0' ELSE (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) * 1.0) / (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Rspec_Rude = 'Y' THEN 1 ELSE 0 END)) END AS Rspec_Vulgar FROM dbo.TekliveQuery
--- Note: How can I avoid the the error message if the dividend and the divisor are both 0?
When I specify a formula between Computed Column Specification, I have twozero values, getting Divide by Zero error, any idea how can I avoid this? Istill want SQL Server to display Zero if it is 0/0, is this possible in SQLServer database?ThanksJ.
In the BI developement, in a matrix i'm trying to avoid division by zero, but even if i test the field before the division, i've got the error everytime.
I put the folowing test condition in the field, but i've got an error.
Ex : iif(Turnover <> 0, Cost/Turnover,0)
But i've got error message when the turnover is 0.
1 ALTER PROCEDURE links_GetAllLinks 2 @startRowIndex INT, 3 @maximumRows INT, 4 @sortExpression nvarchar(50) 5 AS 6 7 8 9 10 11 SET @startRowIndex = @startRowIndex + 1 12 --SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END 13 -- populate the table CAST(getdate() as int) 14 15 16 IF @sortExpression LIKE '' 17 BEGIN 18 SET @sortExpression = 'links_Links.link_date' 19 END 20 DECLARE @sql nvarchar(4000) 21 SET @sql = 'Declare @Links TABLE 22 (RowNumber INT, 23 link_id INT, 24 cat_id INT, 25 cat_title VARCHAR(100), 26 sub_id INT, 27 sub_name VARCHAR(100), 28 link_aproved BIT, 29 link_name VARCHAR(100), 30 link_url varchar(100), 31 link_desc varchar(500), 32 link_date datetime, 33 link_rating INT, 34 link_ratingscount INT, 35 link_ratingvalue INT, 36 link_poster uniqueidentifier, 37 link_comments INT, 38 hit_date datetime);INSERT INTO @Links 39 SELECT ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + '), links_Links.link_id, links_Links.cat_id, links_Categories.cat_title, links_Links.sub_id, links_SubCategories.sub_name, links_Links.link_aproved, links_Links.link_name, links_Links.link_url, links_Links.link_desc, links_Links.link_date, links_Links.link_rating, links_Links.link_ratingscount, (links_Links.link_rating/links_Links.link_ratingscount) as link_ratingvalue, links_Links.link_poster, links_Links.link_comments, links_Links.hit_date 40 FROM links_Links INNER JOIN 41 links_Categories ON links_Categories.cat_id = links_Links.cat_id INNER JOIN 42 links_SubCategories ON links_SubCategories.sub_id = links_Links.sub_id 43 WHERE links_Links.link_aproved = 1;SELECT * from @Links 44 WHERE RowNumber BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1' 45 46 47 48 EXEC sp_executesql @sql 49 50 51 RETURN at row 39, i define a field by dividing to values. I use these for ratings (finding the mean). The problem is, when a new link is submited, it has no ratings, so it performs a divide by zero. how do I check if the column "link_ratingscount" is zero? if it is, then return a zero, and if it is not zero, then do the divide?
I am having a terrible time with a stored procedure. I think it has something to do with a divide by zero issue. Here is my sproc:
ALTER PROCEDURE [dbo].[GetPlayers] @TeamID int AS SELECT PlayerID, TeamID, FirstName, LastName, FirstName + ' ' + LastName As Name, "SPct" = CASE (SELECT Count(PlayerID) FROM GameDetail WHERE AtBatID IN(1, 2, 3, 4) AND GameDetail.PlayerID = Player.PlayerID) WHEN 0 THEN 0 ELSE Cast ( Cast ( ( (SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '1') + (SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '2') + (SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '3') + (SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '4') / (SELECT Count(PlayerID) FROM GameDetail WHERE AtBatID IN(1, 2, 3, 4) AND GameDetail.PlayerID = Player.PlayerID ) * 1.0 ) As int ) As Decimal(4,3)) End FROM Player WHERE TeamID = @TeamID ORDER BY FirstName, LastName
Here is the error I get when I run this: Msg 8115, Level 16, State 8, Procedure GetPlayers, Line 9 Arithmetic overflow error converting int to data type numeric. So the results should look like: .333 or .500 or .000
It's driving me crazy. I have tried everything but cannot get the desired results. I'm hoping someone can help me.
I know you can resolve "divide by zero" messages with a CASE statement.
However, the T-SQL docs suggest that I should be able to simply turn off both the error message and the effect of the error with SET ARITHIGNORE ON and SET ARITHABORT OFF
I am trying to execute these two statements within a stored proc but I need their effect to reach to a select statment that is a sql string (as in 'EXECUTE (@SQLSTRING)' within the same stored proc.
Am I wrong to expect these statements to be able to deal with "divide by zeroes"?
I haven't been able to make it work so I would appreciate any pointers that someone might have.
this is from a SQL playbook i don't understand the logic of DIVIDE when you use more than one NOT EXISTS
such as this query
select distinct itemname from qdel del where not exists (select * from qspl where not exists (select * from qitem where itemtype='n' and not exists (select * from qdel where qdel.itemname=qitem.itemname and qdel.splno=qspl.splno)) and not exists (select * from qdel where qdel.itemname = del.itemname and qdel.splno=qspl.splno))
i don't understand how can NOT EXISTS code brings out the results of the existing rows in those not exists lines like it says NOT EXISTS itemtype 'n' etc. but that is the results!
When I execute the following query, I get a Divide by zero error. I believe the Divide By zero error is occuring in the following statement. How do I avoid the error?
I am writing a report that deals with time in seconds. I am trying to figure out the average handle time for each call. To Get this number i use 5 fields. the formula is
Which translates to ((Incoming Handle Time) + (Work))/(Number of Incoming Calls)
When it does the calculations some times the cs_acd_trk_c + cs_acd_ext_c (number of incoming calls) is zero.
I dont know how to get around this ... i tried to set that value to <>0 in my query but it threw my other numbers off for number of calls which is a different value on the report. Any Help on this would be super appreciated. Thanks, Mitch
