Huge Tables: Minimizing Row Size - Remove Null Bitmap
Apr 21, 2007
I am in the process of designing a SQL 2005 database with tables that may hold several hundreds of millions of rows.
Due to various constraints, I am trying to save as much space as possible by optimizing the size of a row. Currently one row contains the following columns:
byte(4), byte(3), byte(3) = 10 bytes.
Adding 4 bytes for the row header, plus 3 bytes for the null bitmap (as described in BOL) I am ending up with 17 bytes/row. In a real world test it was an average of 18.3 bytes/row.
There are no indexes, no primary key and all columns are not NULL. Hence my question: since no column allows a null value, is there a possibility to "remove" the 3 bytes Null Bitmap?
Is there any other way to shave off one or two more bytes by using a clustered index etc?
Thanks
View 4 Replies
ADVERTISEMENT
Jan 15, 2004
Hi,
My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.
I delete a huge table (1.2 million records * 15 columns).
I checked the db size again. It didnt change.
Shouldn't it decrease because I delete a huge table ??
View 14 Replies
View Related
Jul 14, 2015
I have transnational replication setup on two environments, on one server distribution database is tiny, but on the second server the distribution database is 5 times bigger, and taking up lot of space, both environments have almost same size of data.
View 15 Replies
View Related
Aug 30, 2005
Has anyone implemented split data for an application between two databases because the data size is extremely large? If so could you please point me to relevant information.In this split data scenario, a table will automatically carry over to another database whenever the size limit for the current database is reached. The challenge is here for the DAL (data access layer) to automatically look into the appropriate database when the next row of data is in another database. OR Perhaps there is another solution to this terasize data problem..Any help on this would be greatly appreciated.
View 8 Replies
View Related
Oct 31, 2005
I have the next question, and i would like to hear what do you thinkabout, and if is there a better solution for "my problem"here is the question, I have a huge table with 60GB of data (imagefiles). The problem happen always when i try to ALTER the structure ofthe table. For example I change a field char(3) to char(4)...thesqlserver then performs the "alter table" command...that must besomething similar than "insert into the new table + drop the actualtable" and for that I need about 60GB o space for my LOG file, andtakes hours to complete the operation.Is this the only way to alter a single field in my table??I would like to heard you opinions...Thanks..ALberto
View 2 Replies
View Related
Aug 15, 2012
I need to compare if two developers did the job correctly and created identical tables.
The problem is more complex, but I will try to solve it somehow if I solve the problem of comparing two tables (let them be in different SQL Server 2008 databases) and their properties. No data needs to be compared.
View 6 Replies
View Related
Mar 13, 2007
Actually in my transformation i am transferring huge amount of data.
i have been using oledb command finally to dump my incoming data to respective tables.
For Example :
if you have two tables
table 1,table 2
in my incoming data i have a lookup and check for two unique columns with that of the unique columns in the table 1.if the record does not exsist i try inserting a record into table 2 and get the unique filed of the record and store that in particular column of table 1.
the data is very large an is this the better why or any suggesstions do let me know..
View 5 Replies
View Related
Jul 20, 2005
Hi All,I've the following table with a PK defined on an IDENTITY column(INSERT_SEQ):CREATE TABLE MYDATA (MID NUMERIC(19,0) NOT NULL,MYVALUE FLOAT NOT NULL,TIMEKEY INTEGER NOT NULL,TIMEKEY_DTTM DATETIME NULL,IID NUMERIC(19,0) NOT NULL,EID NUMERIC(19,0) NOT NULL,INSERT_SEQ NUMERIC(19,0) IDENTITY(1,1) NOT NULL)GOALTER TABLE MYDATAADD CONSTRAINT PK_MYDATAPRIMARY KEY (INSERT_SEQ)GOThe TIMEKEY_DTTM field is generated, from the value actually insertedinto theTIMEKEY field, by the following trigger:CREATE TRIGGER TIMEKEY1ON MYDATAFOR INSERT ASBEGINDECLARE @M_TIMEKEY_DTTM DATETIMESELECT @M_TIMEKEY_DTTM = DATEADD(SECOND, INS.TIMEKEY +EP.GMT_OFFSET * 0 ,'1970-01-01 00:00:00.000')FROM INSERTED INS, LOCATIONINFO EPWHERE INS.EID = EP.EIDUPDATE MYDATASET TIMEKEY_DTTM = @M_TIMEKEY_DTTMFROM INSERTED INS, MYDATA MDWHERE MD.INSERT_SEQ = INS.INSERT_SEQENDGOThere is also a composite, non unique, index defined on thetuple:(MID,IID,TIMEKEY,EID)CREATE INDEX IX_METDATA ON MYDATA (MID,IID,TIMEKEY,EID)GOAs a consequence of an application design change, I would also changethis index to be UNIQUE, but when I try to drop and create it I get anerror, because the tables stores some duplicated rows...In order to succesfully upgrade the index definition, I wrote some DMLstaementsto lookup and remove the duplicated rows, keeping only the firstrecord inserted, i.e. the one with the lowest INSERT_SEQ:---- This table stores then umber of duplicated records eventuallydiscovered-- into the MYDATA table; the initial value for the NUM_DUPLICATESfield is-- 0 (no duplicated record)--DROP TABLE DUPLICATESGOCREATE TABLE DUPLICATES (TABLENAME VARCHAR(17),NUM_DUPLICATES NUMERIC(19,0) )GOINSERT INTO DUPLICATES VALUES ('MYDATA',0)GOINSERT INTO DUPLICATES VALUES ('CATEGORIESDATA',0)GO---- ///////// CLEAN UP OF MYDATA TABLE--DROP TABLE TMP_MYDATAGOCREATE TABLE TMP_MYDATA (MID NUMERIC(19,0) NOT NULL,TIMEKEY INTEGER NOT NULL,IID NUMERIC(19,0) NOT NULL,EID NUMERIC(19,0) NOT NULL,INSERT_SEQ NUMERIC(19,0) )GO---- Insert into the TMP_MYDATA table all the duplicated records for-- the tuple (MID,IID,TIMEKEY,EID) and NULL for the INSERT_SEQ field--INSERT INTO TMP_MYDATA (MID,IID,TIMEKEY,EID)SELECT MID,IID,TIMEKEY,EIDFROM MYDATAGROUP BY MID,IID,TIMEKEY,EIDHAVING COUNT(*)>1GO---- Updates the INSERT_SEQ field to the lowest value in the group-- of duplicated records--UPDATE TMP_MYDATASET TMP_MYDATA.INSERT_SEQ = (SELECT MIN(INSERT_SEQ)FROM MYDATAWHERE TMP_MYDATA.MID = MYDATA.MID ANDTMP_MYDATA.IID = MYDATA.IID ANDTMP_MYDATA.TIMEKEY = MYDATA.TIMEKEY ANDTMP_MYDATA.EID = MYDATA.EID )GO---- Updates the value of NUM_DUPLICATES for the MYDATA table.--UPDATE DUPLICATESSET NUM_DUPLICATES = (SELECT COUNT(*) FROM TMP_MYDATA)WHERE TABLENAME = 'MYDATA'GO---- Delete from the MYDATA table all the duplicated records,-- keeping only the row with the lowest INSERT_SEQ-- The delete is performed only if there are duplicated recors;-- this is achieved using a "short circuit" AND on the number ofrecords-- stored into the NUM_DUPLICATES field of the DUPLICATES table for-- the MYDATA table...--DELETE FROM MYDATAWHERE ( SELECT NUM_DUPLICATES FROM DUPLICATES WHERE TABLENAME ='MYDATA') > 0 ANDEXISTS ( SELECT 1FROM TMP_MYDATAWHERE MYDATA.MID = TMP_MYDATA.MID ANDMYDATA.IID = TMP_MYDATA.IID ANDMYDATA.TIMEKEY = TMP_MYDATA.TIMEKEY ANDMYDATA.EID = TMP_MYDATA.EID ANDMYDATA.INSERT_SEQ > TMP_MYDATA.INSERT_SEQ )GOThis tecnique works fine on a normal table (1M recs) but is not veryperformanton huge tables (>10M records)!Do you know a better way to achieve the task of removing all theduplicates records, preserving the lowest INSERT_SEQ betwee theduplicates and also preserving the sequence seed, so that a new recordinserted at time t1>t0 is enumerated with an INSERT_SEQ|t1 >max(INSERT_SEQ)|t0 ?Thanks a lot for your help!PatrizioPS. sorry for such a large post!
View 1 Replies
View Related
Oct 12, 2006
Hi,
I have a situation where I have 4 tables:
1. 2 Dimensional tables(Parent), DIM1 with 50000 rows, and DIM2 with 1000 rows
2. Fact 1 with 50 columns, 25 Million rows and with FK to DIM1 and DIM2
3. Fact 2 with 40 columns, and 25 Million rows and with FK to DIM1 & DIM2 tables.
Actually the fact 1 and fact 2 have same related data but since our Analysis cube person wanted the fact table not to have more than 50 columns we divided the tables into 2, but they have the same compound key.
Above said, I have a situation where I have to select all the columns, in both fact tables, and do a group by. I wrote the query and ran "Analyze Query in the Database Engine Tuning Advisor" for it. It gave bunch of recomendations about the statistics and indexes which I created. When I executed the query the result came up in matter of seconds, which was good.
In the query I had a condition having MarketName='Bridgeview' and DateID = 344 (FK of today-1).
When I wanted the data for last 30 days I changed to DateID in ( > FK of today -32 and < FK of today), the query responded and worked fine.
But when I changed the query to get MarketName='Aurora' (other than I used when I ran Tuning Advisor), the result returned is empty set. When I removed the MarketName condition, it is supposed to return all markets' data, but it returns only Bridgeview data.
I know the data is in the table for all markets, since reports are rendered from these fact tables for all of these markets(also ran queries to check the fact table data).
I am unable to point out the reason why the query behaves like this. It responds to the date change, but not to the MarketName change.
I really appreciate if anyone can help me point out the problem.
Thanks,
Venkat
View 3 Replies
View Related
Nov 19, 2015
I have many tables, and I just want to print the relationships between them. Â The ones without foreign keys to primary key relations are irrelevant. Â I made a diagram of all tables in sql server management studio, and it shows the key relations, but its a very large diagram horizontally and vertically. Â Is there a way to print the whole thing so that it doesn't take endless pages that I don't know how to piece together?
View 3 Replies
View Related
Aug 20, 2004
I've 2 columns after executing query,
one column from those 2 is calculated column.
I am getting resultset as follows
ACCOUNT ¦(calculated column)
------------------------------
100 ¦alpha
100 ¦NULL
My concern is how should I remove record with NULL from final resultset ?
If I give some name to that calculated column say 'Name'
and tried to put in main query as
"where 'Name' IS NOT NULL"; it doesnot work.
Can you have any workaround for it?
:confused:
View 2 Replies
View Related
Jan 17, 2015
I have a pivot query returning data ordered by a timestamp. In any give second only a few of the columns will have data, the remaining columns are NULL. I would like to default the NULL data values to the previous NON NULL value by timestamp.
Current Pivot Table:
Time RowA RowB RowC
12:15:12 NULL NULL 50
12:15:11 97 NULL NULL
12:15:10 98 25 51
12:15:06 101 NULL 50
12:15:05 100 NULL NULL
What should be returned:
Time RowA RowB RowC
12:15:12 97 25 50
12:15:11 97 25 51
12:15:10 98 25 51
12:15:06 101 NULL 50
12:15:05 100 NULL NULL
I know that I can do this user a cursor and handling the output is code, but I would prefer to do this in a single set based query.
View 1 Replies
View Related
Jun 19, 2001
We have a WEB site and my BOSS wants that It should work like me (24x7). Any time when one SQL Server go down , another should take its place. What will be the best way to do this? Can any body guide me in DETAIL. or refer to me at any good material (STEP BY STEP)
Thanks
View 1 Replies
View Related
Aug 20, 2004
Hello all,
I am working on an Access 2000 project that is used to generate reports on archived sales data. This app uses linked tables that are pointing to some SQL 2K tables. Some of the queries are large and end up creating a table lock when executed, blocking all other users from using the app until it has finished executing the query. I would like to have queries that read data ignore this lock on the table but do not know of a way to do this using linked tables in Access 2000. I would use a NOLOCk table hint but need something else for this issue.
Any ideas?
Thanks
View 1 Replies
View Related
Oct 8, 2007
Hi All,
I have a DB of transaction log grown to 19GB. I have some questions.
1. Is there any way that we can minimize the entries to transaction log.
2. If i use Transactions in my stored procedure will it be usefull to automatically remove the transaction entries in transaction log once after the commit transaction is given.
3. Is there a way to reduce my current transaction log and not to grown in such a manner futher.
Thanks in Advance
Sri
View 1 Replies
View Related
Apr 3, 2000
SQL 7 SP1 NT4 SP5
I have a TRANSACTION table with 150 million rows.
I have a USER table.
Each user has about 600 records in the TRANSACTION table.
The TRANSACTION cluster index is on USERID + RECID . The second index is on USERID + Fieldx + Fieldy.
The TRANSACTION table gets about 1.4 million inserts in a normal day and about 40,000 updates.
I want to go through the USER table and delete all users who have not visited me in a while.
I want to do this without substantially hindering performance in a production environment. I can perform this over a week period or two if needed.
The best way I thought of doing this was to grab x amount of users in a cursor and loop through deleting their corresponding TRANSACTION records.
Does anyone have any ideas on a better way. What is going to happen to my indices during this time ?
Thanks !!!
View 3 Replies
View Related
Jul 8, 2015
Does null column take storage in table ? I created the following table:
drop table [dbo].[TBL_orig]
CREATE TABLE [dbo].[TBL_orig](
[1] [bigint] NOT NULL,
[2] [bigint] NULL,
[3] [bigint] NULL,
[4] [bigint] NULL,
[5] [float] NULL,
[Code] ....
Does null value will take a place even no data insert to this columns and if yes this answer is 4 mb is logical gap ?
View 4 Replies
View Related
May 4, 2015
I have a main table called Stock for example.
One process ( a service ) inserts data into this table , as well as updates certain fields of the table periodically.
Another process ( SQL Job ) updates the table with certain defaults and rules that are unknown to the service - to deal with some calculations and removal of null values where we can estimate the values etc.
These 2 processes have started to deadlock each other horribly.
The SQL Job calls one stored procedure that has around 10 statements in it. This stored proc runs every minute. Most of them are of the form below - the idea being that once this has corrected the data - the update will not affect these rows again. I guess there are read locks on the selecting part of this query - but usually it updates 0 rows - so I am wondering if there are still locks taken ?
UPDATE s
SET equivQty = Qty * ISNULL(p.Factor,4.5) / 4.5
FROM Stock s
LEFT OUTER JOIN Pack p
on s.Product = p.ProductId
AND s.Pack = p.PackId
WHERE ISNULL(equivQty,0) <> Qty * ISNULL(p.Factor,4.5) / 4.5
The deadlocks are always between these statements from the stored procedure - and the service updating rows. I can't really see how the deadlocks occur but they do.
Another suggestion has been to try and use an exists before the update as below
IF EXISTS( SELECT based on above criteria )
( UPDATE as before )
Does this reduce the locking at all ? I don't know how to test the theory - i added this code to some of the statements, and it didn't seem to make much difference ?
Is there a way to make a process ( in my case the stored procedure ) - give up if it can't aquire the locks rather than being deadlocked - which leads to job failures and emails etc ?
We are currently trying to filter down the data that is updated to be only the last few months - to reduce the amount of rows even analyzed - as the deadlocking does seem to be impacted by the number of rows in the tables.
View 9 Replies
View Related
Dec 11, 2007
I made a copy of our database and restored it to a new database. We want to strip all the content out except the users and roles. I have been able to delete everything except "system" tables, views and stored procedures.
Can anyone help me get rid of these?
Thanks - Kelli
View 5 Replies
View Related
Aug 27, 2004
Hi
I was using bitmap index in Oracle and now moving to SQL Server 2000. Is there any "equivalent" of bitmap index in sql server ? Any help / links ?
Thanks,
Sam
View 2 Replies
View Related
Aug 7, 2007
I need to find out all columns that are identity. How do I query syscolumn to find out this information.
Thanks.
View 6 Replies
View Related
Nov 12, 2007
Can a bitmap be added to a Varbinary field using the INSERT statement. If not what other method can I use to add a photo image (bmp or gif) to my Sql database?
View 4 Replies
View Related
May 4, 2007
Hello,
I have not seen a response to this specific question so here goes. I had working code for loading an image into my smart device SQLCE db and for retrieving images. But after upgrading my application to SQLCE 2.0 I am unable to use my retrieve code. This is what was previously working,
Dim ImageBytes As Byte = rs.GetValue(rs.GetOrdinal("OLE"))
Dim ImageStream As New System.IO.MemoryStream(ImageBytes)
frmFlashcardActive.picboxBlobToPicBox.Image = New Bitmap(ImageStream)
Help!
This is what I successfully use to save the image to the database
Dim PictureInfo As New FileInfo("My DocumentsUSAFlag.bmp") 'OpenDialog.FileName
Dim PictureSize(PictureInfo.Length) As Byte
If PictureSize.GetValue(0) <= 69 Then
Dim fsPicture As New FileStream(PictureInfo.FullName, FileMode.Open)
Dim PictureReader As New BinaryReader(fsPicture)
PictureReader.Read(PictureSize, 0, PictureSize.Length)
'find the previous record and add the picture
rs.SetValue(rs.GetOrdinal("OLE"), PictureSize) 'could be FlashcardAPic
rs.SetValue(rs.GetOrdinal("Picture"), "True")
rs.Update()
fsPicture.Close()
View 1 Replies
View Related
Jul 13, 2015
I have 2 tables below:
Table 1:
Product No Quantity
A 1
B 2
C 3
Table 2:
Product No Grade Quantity
A Good
 A Normal
 A Bad
 B Good
 B Bad
 C Good
C Normal
C Bad
In Table 2, Product No divided by Grade. I want to lookup the Quantity from Table 1 to Table 2. The same Product No will have 1 value, the other value is 0. The result for Column Quantity should be like this:
Table 2:
Product No Grade Quantity
A Good 1
A Normal 0
A Bad 0
B Good 2
B Bad 0
C Good 3
C Normal 0
C Bad 0
View 8 Replies
View Related
Jun 2, 2015
How can I resize a bitmap to fit the screen? I have inserted a bitmap using an external source.
View 4 Replies
View Related
Mar 17, 2008
Hi,
Problem:
I'm using C# 2005. The data type of the bitmap file saved to database is "long binary data". It should be "OLE Object".
Source Code:
Code Snippet
Bitmap myBitmap = new Bitmap(@"c:Picture1.bmp");
OleDbConnection conn = new OleDbConnection("<<My Connection String>>");
conn.Open();
string sql = "UPDATE Table1 SET fldOLE = @fldOLE WHERE fldID = 5";
OleDbCommand command = new OleDbCommand(sql, conn);
command.Parameters.Add(new OleDbParameter("@fldOLE", myBitmap));
int rowsAffected = command.ExecuteNonQuery();
Question:
How can I save Bitmap to database with data type "OLE Object"?
View 3 Replies
View Related
Aug 4, 2007
Could not clear 'DIFF' bitmap in database due to error 9002 . A subsequent backup operation may be slower/larger than normal .
View 1 Replies
View Related
Jan 21, 2000
I am finding that the size of my SQL Server 7 tables are approx 75% LARGER than the same Visual FoxPro table. I am not considering indexes in either one. The only difference is that the SQL Server table has a timestamp that the Visual FoxPro table does not.
Is this normal? or did I do something wacko?
I upsized the data from Visual Fox to SQL by appending the info in the Visual Fox table into a remote view and then doing a TABLEUPDATE to send it to SQL Server.
Any help would be appreciated.
Rick
View 2 Replies
View Related
Nov 16, 2001
I'm having to do some estimation on the size of the db that I will eventually create.
I have a 6.7 million row table that holds 5 integers and a varchar(8).
Therefore my crude maths are that an integer is 4 bytes and a varchar is max 8 and therefore each line is 28 maxmimum bytes long. Multiply by 6.7 million and you get 1876000000 bytes or 183203KB or 179MB's.
With me playing with this table the db has balooned from 23mb to 1,325mb. This doesnt work out. One possible explanation is if SQL Server does the same as Access and you need to compress to gain the space back. I wasn't aware of this though. I have filled and emptied the table several times over to test the import routine.
I therefore have a few questions.
1. Why has the db ballooned to this size?
2. How do I accurately work out future db/table sizes?
3. Is there a way to work out the size of a table?
4. Do SQL db's have to be compacted like Access's?
Help appreciated.
View 2 Replies
View Related
Jun 5, 2008
Hi,
I know I can check individual table's properties for the size and row count of each, but is there a way of doing it for all tables within a database in SQL server 2005 ? I have about 69 tables in one..
Thanks,
Raddy
View 2 Replies
View Related
Nov 8, 2006
Hi,
I have a database which is 25 GB in size. The database has few tables.
Can I individually find out which table is occupying more space?
Thanks
Santhosh
View 3 Replies
View Related
Sep 4, 2007
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
View 1 Replies
View Related
Dec 11, 2013
I am using the QRCode4CS class [URL] .... to generate QR codes.
I can use the following code to successfully return a bitmap image to a picturebox in a Windows Form Application.
public class CreateQRCodeClass
{
public static Image CreateQRCodeImage(string inputString)
{
QRCode4CS.QRCode qrcode = new QRCode4CS.QRCode(new QRCode4CS.Options(inputString));
qrcode.Make();
[Code] ....
In trying to adapt the same code (below) to display a QR code in an SSRS report I get the error "There is an error on line 1 of custom code: [BC30311] Value of type 'System.Drawing.Image' cannot be converted to '1-dimensional array of Byte.'
Here is the custom code I am using.
Public Function QRCode(ByVal RetailerId As String) as Byte()
Return QRCode4CSCreateQRCode.CreateQRCodeClass.CreateQRCodeImage(RetailerId)
End Function
Here is the revised custom assembly.
public class CreateQRCodeClass
{
public static byte[] CreateQRCodeImage(string inputString)
{
QRCode4CS.QRCode qrcode = new QRCode4CS.QRCode(new QRCode4CS.Options(inputString));
qrcode.Make();
Image canvas = new Bitmap(86, 86);
[Code] ....
What data type can I successfully return to SSRS to display the image?
View 9 Replies
View Related
