Table w/ large number of columns
-
I have a table that contains over 400 columns in it consisting of mostly integer datatypes. When I open the table through Enterprise Manager or try to query against the table, it is very slow even though I have only 600 records. Outside of creating multiple tables instead of one table, is there a way to improve performance when query against this table and pulling back all columns? I have a clustered index on the field most commonly queried on, but that does not seem to help. Is this just a SQL Server limitation or is there something I can do to help speed things up.
-
Interesting configuration
I created a table to match and inserted about 500 rows then doing a select from that table brings back all records in about 4 seconds. Is that what you are seeing?Out of curiousity, what is this table used for? Limited experience here but I have never seen anything with that many col's.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
I havent tested, but I can see where it might be slower, especially compared to smaller tables. 400 cols x 4 bytes per col for ints, 1600 bytes (more or less) per record gives you about 5 records per page - you'd have to do a lot more IO than you'd expect for 600 records. That said, once it's cached it should still be pretty quick - do you see a difference between running it the first time and the second time? Have you tried the same query in Query Analyzer?
David, I'll give you credit for testing it! What did you use to generate the table and test data?
Andy
-
You can easily generate the table by using dsql or alter table statements to add columns.
You can also generate an inserts by doing a select from syscolumns.
I tried it and didn't get any problems.
Maybe the table is very fragmented - try rebuilding the clustered index. But I wouldn't expect it to take long anyway.
Is it returning the dta to you that's the problem?
try a select count(fld) on a non-indexed field.
try also putting a non-clustered index on a field and just selecting that field so that it doesn't access the data pages.
Edited by - nigelrivett on 11/30/2001 11:40:29 PM
Cursors never.
DTS - only when needed and never to control. -
Sure, but did he copy/paste the line 399 times? Use a cursor, cross join, dmo, ?
Andy
-
The table I am talking about is for a paper mill. The reason this table has over 400 columns is that it holds data point readings from a PLC that is processing data as veneer (plywood) passes under it. There are 12 counters on this PLC that process 400 readings every second for each piece of wood. These 400 readings show the people in Quality Control exactly just how good the wood is when it passes through their system. The problem does not seem to be from how many records b/c when I select certain fields instead of all fields the results come back very quick. It is just extremely slow when I say select * from table or go to Enterprise Manager and do Open All Rows for that table. Unfortunately, the end user will be querying all columns from this table most of the time so they can see all readings at one time. Is there something I can tune on the database level to utilize more memory than since the data pages will be quite large when pulling back this table?
-
Not really. If you're overall system is memory constrained, then adding more memory might help, but I would not try that as the first fix. What speed is your network connection? A slow connection (or a slow card either on server or client) will affect performance - the user perception of it anyway. Another ideas you might look at is do you really need ints, could you use a small int or a tiny int? This would reduce the row size and number of bytes you're sending across the network.
Exactly how slow is it?
Andy
-
quote:
It is just extremely slow when I say select * from table or go to Enterprise Manager and do Open All Rows for that table.Are you trying to do the select all at the same time data is being inserted into the table?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
>> Sure, but did he copy/paste the line 399 times? Use a cursor, cross join, dmo, ?
Does it matter? Whatever you find easiest and generates reasonable data - I usually just use random numbers for all fields in something like this unless I know the distribution expected.
Given that it is quick when you just select certain fields then unless you have a covering index it must be accessing all data pages (see my previous comment).
Then the difference is just the presentation.
Try a select * from a monitor connected directly to the server to get the network out of the equation.
You are probably constrained by the network about which there is probably little you can do easily except tell the users they should query as little data as possible.
You could try tuning the network but that's outside my area of expertise.
Cursors never.
DTS - only when needed and never to control. -
Nope, doesnt matter, other than Im curious as to how it was done, more about the table creation than the data.
Andy
-
Two immediately obvious ways are
(this is they way I usually do it)
create table #a(s varchar(100))
declare @i int
select @i = 0
while @i < 400
begin
select @i = @i + 1
insert #a select 'int' + right('000' + convert(varchar(3),@i),3) + ' int ,'
end
select * from #a
From the result add a create table statement to the start , knock off the comma at the end, a few brackets and correct any mistakes and you are there.
This way is handy for temp tables in dsql in SPs.
create table a (int001 int)
declare @s-2 varchar(100)
declare @i int
select @i = 1
while @i < 400
begin
select @i = @i + 1
select @s-2 = 'alter table a add int' + right('000' + convert(varchar(3),@i),3) + ' int '
exec (@s)
end
Edited by - nigelrivett on 12/02/2001 2:51:00 PM
Cursors never.
DTS - only when needed and never to control. -
DMO tends to be more verbose than TSQL, but I thought I'd see how it turned out anyway. Took me just over 10 mins to throw this together.
Dim oServer As SQLDMO.SQLServer
Dim oDatabase As SQLDMO.Database
Dim oTable As SQLDMO.Table
Dim oColumn As SQLDMO.Column
Dim oIndex As SQLDMO.Index
Dim J As Integer
'connect using trusted connection
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect "."
End With
'get reference to database
Set oDatabase = oServer.Databases(DBName)
'create a table
Set oTable = New SQLDMO.Table
'throw in a col to use as the pkey
Set oColumn = New SQLDMO.Column
With oColumn
.Name = "BogusPkey"
.Datatype = "int"
.Identity = True
.AllowNulls = False
End With
oTable.Columns.Add oColumn
Set oColumn = Nothing
'now start creating column objects and adding
'to the columns collection
For J = 1 To NumberofColumns
Set oColumn = New SQLDMO.Column
With oColumn
.Name = "COL_" & Format$(J)
.Datatype = "int"
.AllowNulls = True
End With
oTable.Columns.Add oColumn
Set oColumn = Nothing
Next
'give the table a name
oTable.Name = TblName
'add to tables collection
oDatabase.Tables.Add oTable
'clean up
Set oTable = Nothing
Set oDatabase = Nothing
oServer.DisConnect
Set oServer = Nothing
Andy
-
I probably couldn't type that in 10 mins.
The two examples I gave you took a couple of minutes each (but then I haven't run them.
Just by the amount of code there I don't think that would be a good solution for a quick test table.
Cursors never.
DTS - only when needed and never to control. -
I agree, is not the fastest.
SMC,
Dont know why I didnt ask earlier, if your hardware has 12 counters, why not change the table to have 12 columns? It's at tradeoff depending on your requirements for viewing the data.
Andy
-
O.k. Back to work... Now to solve the mystery. No, I did not copy and paste nor did I do anything as robust as what you guys did. I just used a while loop with a print statement in T-SQL to create a formatted output and then copied the result set into the create table and insert statement. Not the prettiest but functional.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
I created a table to match and inserted about 500 rows then doing a select from that table brings back all records in about 4 seconds. Is that what you are seeing?Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply