When Or Where Does To May Columns Or Tables Affect Performance?
May 4, 2001
I have a db which I have little control over most of it's makeup because of the vendor supplied tools. We currently have over 700 tables and 19000 columns. Has anyone seen a problem or saturation pont with these kinds of numbers? The database delivered to the clients will be from 2-50 gig depending on the site. I can probably through hardware at problems, but if anyone has been down this road any suggestions are appreciated.
View 1 Replies
ADVERTISEMENT
May 23, 2008
We have more than 2000 tables in the database. Can existence of so many tables affect the performance on SQL SERVER 2005?
View 1 Replies
View Related
Aug 7, 2001
We think we're having performance problems, and among the areas of investigations is the tempdb database. Since it resets itself after SQL is restarted, is there a way to find out how big it has grown in the past ? Does leaving it at the default size cause a performace hit ?? Right now it's 8.75 MB, with 7.38 MB available, which sounds pretty harmless.
Any thoughts ?
View 1 Replies
View Related
Jun 23, 2006
Everywhere I read, it states that running SQL Profiler can affect performance of your SQL Server. My question is - how much of an impact will it really make? Will I see a 1% degredation in peformance? 5%? 50%? I haven't been able to find a good answer. We currently have SQL Profiler running all day long for almost 3 years, and the databases are still humming.
Is it the amount of data you are requesting from the trace that affects performance? There are some compliance tools out there (Idera Compliance Manager, IPLocks, etc) that run a profiler trace to get data. There are other DBAs in my organization who don't want to use them because "profiler traces will degrade my SQL Server performance". How true is this really.
Any help I can get would be extremely appreciated.
Thanks.
View 4 Replies
View Related
May 20, 2008
Does multiplication with 1 affect query performance?I have a a stored procedure that converts results to another unit if required. In alternative 1 below, the results are returned with a separate select statement if no conversion is necessary - in other words, no multiplication with a conversion factor is required. However, the code is not very nice since I need to repeat the select statement again in case a conversion is required, this time including the conversion factor.Alternative 2 uses cleaner-looking code. The conversion factor is set to 1 if no conversion is required, and a single SELECT statement is used to return the data. The @factor variable is defined as a float.I would rather use alternative 2, but I wonder if there is any performance penalty for doing that if no conversion is required since the results are always multiplied with the @factor? Or can SQL server somehow understand that @factor = 1 and no multiplication is required?--- Alternative 1: ---IF @fromunit_sid = @tounit_sid-- Return unconverted results
SELECT ISNULL(ls_totalWaterConsumption,0) AS ls_totalWaterConsumption,ls_theoreticalWaterConsumption AS ls_theoretical_WaterConsumption,ls_totalWaterConsumption - ls_theoreticalWaterConsumption AS ls_extra_WaterConsumption FROM Results WHERE scenario_id = @scenario_idELSEBEGIN
-- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT -- Get the converted results
SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_idEND --- Alternative 2: ---IF @fromunit_sid = @tounit_sidSET @factor = 1ELSE
-- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
-- Get the converted results
SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_id And another question: is using an IF function considerably faster than making a call to another stored procedure?In alternative 2 above I use an IF statement to check if @fromunit_sid = @tounit_sid, and . But in fact the function getConversionFactor that I'm calling does exactly the same thing: if I pass in identical from- and to-values, it simply returns 1, so I could omit the IF statement completely and just use alternative 3. But is it slower?--- Alternative 3 -- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
-- Get the converted results
...
View 3 Replies
View Related
Mar 27, 2006
I have two instances installed on my server. I have one database on the default instance and two very large reporting databases on the other instance.
Will having the default instance running affect the performance of the non-default instance where my reporting databases are?
View 1 Replies
View Related
Jul 23, 2005
I'm considering adding domain integrity checks to some of my database tableitems. How does adding such constraints affect SQL Server performance? Forexample, I have a simple constraint that restricts a couple of columns tohaving values within the values assigned in my application by anenumeration:(([Condition] >= 0 and [Condition] <=3) and ([Type] >= 0 and [Type] <=2))This enforces domain integrity for two enumerations having values 0, 1, 2, 3and 0, 1, 2 in the application. Is this an efficient way of performing suchchecks? What are the pitfalls of domain integrity checking?ThanksRobin
View 1 Replies
View Related
Jul 20, 2007
Recently we added a new table into our SQL2000 database specifically to store scanned in images of documents. This new table contains a PK field, a couple of datetime fields, a couple of char(1) fields and one 'image' field.
Before adding this table, the database size was approx 6GB. Six months after adding this new table, the database has grown to 18GB - 11GB of this is due to the scanned in images.
Would this new table affect the SQL performance with regards to accessing other data in the database that has nothing related to the new table?
If so, would moving this new table into it's own database be recommended?
Thanks
Rod
View 1 Replies
View Related
Apr 25, 2007
Hi,I'm designing a new database and I have a doubt in which surely youcan help me.I'm storing in this database historical data of some measurements andthe system in constantly growing, new measurements are added everyday.So, I have to set some extra columns in advance, so space is availablewhenever is needed and the client doesn't have to modify the structurein SQL server.The question is: the more columns I add "just in case", the slower theSQL reads the table?Of course the "empty" columns are not included in any query until theyhave some valid data inside.Will I have better performance if I configure only the columns beingused at the moment, without any empty columns?Thanks in advance.Ignacio
View 2 Replies
View Related
Dec 28, 2007
Hi,
I have a denormalized table (done so with reason) with around 40 columns. I would never have to retrieve data for all of those columns together.
I haven't done any performance measurements yet but just wondering if anyone has ready answer to this: Will there be a performance degradation if I retrieve data from a table with many columns, even if not all columns are referred in the query? (for making it simple, lets assume that all or varchar type of columns, I just want to find out if performance degrades if there are too many columns in table)
Thanks in advance,
Sandeep
View 1 Replies
View Related
Jan 6, 2006
I am doing a mass update of our SQL script files by adding dbo.to all references to the tables.The code is also adding dbo. in front of existing lines of codethat are like this:SELECT CLIENT.FIRSTNAME,CLIENT.LASTNAMEFROM CLIENT....The mass update is causing this to become:SELECT dbo.CLIENT.FIRSTNAME,dbo.CLIENT.LASTNAMEFROM dbo.CLIENT....My goal is to see FROM dbo.CLIENTbut even the column names are getting the "dbo."added, so my question is if there's any performanceloss or any other side effect if I end up all my columnswith a preceeding "dbo."?Thank you
View 4 Replies
View Related
Apr 20, 2014
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
View 0 Replies
View Related
May 25, 2004
I am planning an application where ~1000 companies will be accessing data. Should I use a key to identify the company and place all data in one table i.e (WHERE company =123) or should the application create company specific tables i.e should I have 1000 small tables with 100 records in each, or one table with 100,000 records?
View 2 Replies
View Related
Mar 17, 2001
I have been researching some performance problems in a very large
application and I have a couple of questions about temp tables. (SQL 7.0
SP2)
I have one large procedure that I have been using as a test case.
Originally this procedure was a cursor with lots of processing steps
involving writing to, reading from and deleting in temp tables inside the
cursor. I remember reading that temp tables inside a cursor were a
potential performance problem, so I rewrote the procedure, replacing the
cursor with a While Loop.
Doing this showed no increase in performance. Since Profiler was showing .5
second duration times on statements in the procedure accessing the temp
tables I tested some more. I moved all the create statements to the top of
the procedure, as I know these statements after processing steps can cause
recompiles to happen. Still no performance increase.
Finally I replaced all the temp tables with actual tables, just to see what
would happen. With no other changes the performance increased by more than
500%.
Can someone give me some clues as to what is happening here, because if this
is a symptom of something I don't understand, the potential performance
problems from other places where temp tables are similarly used in the
application are enormous.
Thanks.
View 1 Replies
View Related
Sep 9, 2003
In a simple join query, such as
SELECT *
FROM a, b
WHERE a.id = b.id
if table 'a' has 1 million and table 'b' has few thousands of records, will the order of the tables in from class will make any difference?
View 1 Replies
View Related
Nov 20, 2007
Hi There
I have a table lets call it TABLE_A that has +- 100 million rows , obviously inserts into this table take some time as it has 1 clustered and 3 non clustered indexes.
I have another table lets call it TABLE_B, it is identical to TABLE_A and it holds 100,000 rows that must be inserted into TABLE_A.
As you can imagine a : INSERT INTO TABLE_A select * from TABLE_B takes alot of time.
What is the best way to speed this up? (Dopping indexes in not an option).
I know bulk insert gives the best performance, but can you bulk insert between tables ? Bulk insert in from a flat file source.
It seems redundant to write an ssis package to extract the data out of TABLE_B to file simply to bulk insert in back into the database?
So in a nutshell what is the fastest way to get the rows from TABLE_B in TABLE_A?
Thanx
View 1 Replies
View Related
Aug 15, 2006
Hi everyone
I need a solution for this query. It is working fine for 2 tables but when there are 1000's of records in each table and query has more than 2 tables. The process never ends.
Here is the query
(select siqPid= 1007, t1.Gmt909Time as GmtTime,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as EngValue,
t1.Loc1Time as locTime,t1.msgId
into #temp5
from #temp1 as t1,#temp2 as t2,#temp3 as t3,#temp4 as t4
where t1.Loc1Time = t2.Loc1Time and t2.Loc1Time = t3.Loc1Time and t3.Loc1Time = t4.Loc1Time)
I was trying to do something with this query.
But the engValues cant be summed up. and if I add that in the query, the query isnt compiling.
(select siqPid= 1007, t1.Gmt909Time as GmtTime,
t1.Loc1Time as locTime,t1.msgId,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue
--into #temp5
from #temp1 as t1
where exists
(Select 1
from #temp2 as t2
where t1.Loc1Time = t2.Loc1Time and
exists
(Select 1
from #temp3 as t3
where t2.Loc1Time = t3.Loc1Time and
exists
(Select 1
from #temp4 as t4
where t3.Loc1Time = t4.Loc1Time))))
I need immediate help on that, I would appreciate an input on it.
Thanks
-Sarah
View 15 Replies
View Related
Jan 5, 2001
We have some tables that we have spread across two databases. The segregation isn’t essential, but the entities involved were disparate enough that we thought it made sense. However, our client app regularly & frequently requires information that can only be answered by queries to tables in both databases. It has been suggested that segregating the tables as we have introduces a performance hit. At this stage, it would be relatively easy to re-combine the tables into one DB.
View 1 Replies
View Related
Jan 25, 2008
Hi gurus, I'm creating a web application where I will have a large number of tables (between 10k and 20k), this is done for the sake of scalability as tables will be moved to different database servers as the application grows and also for performance (smaller indexes). I'm worried though how having a large number of tables could affect the performance of SQL Server as the application will start on one single database server. I tried to find some resources on that on the internet but couldn't find any.
I would really appreciate if you can give me some advice and if you have any good links that would be great...
View 10 Replies
View Related
Jan 25, 2008
Hi gurus, I'm creating a web application where I will have a large number of tables (between 10k and 20k), this is done for the sake of scalability as tables will be moved to different database servers as the application grows and also for performance (smaller indexes). I'm worried though how having a large number of tables could affect the performance of SQL Server as the application will start on one single database server. I tried to find some resources on that on the internet but couldn't find any.
I would really appreciate if you can give me some advice and if you have any good links that would be great...
Waleed Eissa
http://www.waleedeissa.com
View 9 Replies
View Related
Nov 13, 2007
Hi
I have a question about the partitioning a table.
I have a database with more 50 tables and 25 tables are having more than 10 lakhs records which includes history records.I have two data files for this database under PRIMARY FILE GROUP.Now i want to transfer these history records to some other database.
I wanted to know if this kind of activity will boost the database performance?.If yes how should i configure my new database.
On what factors of partitioning my performance will boost.
Thanks in advance
Regards
Arvind
View 1 Replies
View Related
Dec 5, 2007
Hi,
I have a table with over 61 million records having a clustered index on an identity column(Primary key). Simple count queries are taking minutes to execute on this table (ex: select count(1) from table1). I have checked the statistics on the primary key which displayed me the histogram having the 39th million record as the Range-hi-key. I updated the statistics on this column and tried requerying, but still it took atleast 5 minutes to give me the count of records in the table. Also, there were no users using the table when I queried. Inserts into this table were working fine. I have other tables in my database with 41 million records having no such issues. Can anyone point me to the problem areas in such scenarios?
Thanks,
Harish
View 6 Replies
View Related
Nov 20, 2007
Will you recommend the usage of temporary tables in a SQL server database ? AFAIK, it boosts the performance. But recently I read one article in SQL Server performance.com[^] which confused me. Any insights on this would be helpful ?
Thanks
View 3 Replies
View Related
Jul 8, 1999
After upgrading to SQL 7 (SP1), we have several SP's that have gone from taking 2-3 min to take 15-20. Each of these SP's creates at least one temp table, inserts into that table, then updates the records in that table. From our research, we can tell that the creation and inserts into the temp tables are fine. It is the updating of these tables that causes the problem. We can observe that the problem is happening by watching the processors go to and stay above 90%. If it were just a few SP's, we could easily fix it and go on, but because of 6.5's limit of 16 tables referenced in a SP, we had to use this method many times. Is there a fix out there for this or a configuration change I can make?
View 2 Replies
View Related
Jun 13, 2004
The following code should insert into 3 tables based on conditions. There's something screwy in my syntax and I'm pretty new at this can anyone help with transforming this in terms of performance and being syntactically correct? Thanks a million!
CREATE PROCEDURE [insert_vwMusic]
(@Artist [nvarchar](50),
@Genre [nvarchar](50),
@NLink [nvarchar](50),
@Album[nvarchar](50),
@Song[nvarchar](50),
@ArtistID[nvarchar](50),
@AlbumID[nvarchar](50),
@SLink[nvarchar](50))
AS
DECLARE @NewArtistID VarChar(50),
DECLARE @NewAlbumID VarChar(50)
IF Not Exists (SELECT [Artist] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
BEGIN
INSERT INTO [integration].[dbo].[tblMusic_Artist]
( [Artist],
[Genre],
[NLink])
VALUES
( @Artist,
@Genre,
@NLink)
SET @NewArtistID = @@IDENTITY
INSERT INTO [integration].[dbo].[tblMusic_Albums]
( [Album]
VALUES
( @Album)
SET @NewAlbumID = @@IDENTITY
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
ELSE
BEGIN
IF Not Exists (SELECT [Album] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
BEGIN
INSERT INTO [integration].[dbo].[tblMusic_Albums]
( [Album]
VALUES
( @Album)
SET @NewAlbumID = @@IDENTITY
SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
END
ELSE
BEGIN
SET @NewAlbumID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
GO
View 5 Replies
View Related
Jul 20, 2005
We have a need to retrieve Sybase data within a MS SQL Serverapplication. We are using SQL Server's linked database feature withthe Sybase 12.0 OLE DB driver. It takes 5 minutes to run a query thattakes 2 seconds from isql.Any suggestions?Thanks
View 1 Replies
View Related
Jun 21, 2007
Hello All,
When creating my database I have modeled some of the tables after the Adventureworks sample database.
There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).
In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.
Thanks for your help!
View 1 Replies
View Related
Apr 4, 2008
Hello,
I have 3 tables (A, B, C) with milions of records (A ca 5 milions, B and C ca 10 milions).
I have created a join betwenn them
select some fields (A, B, C)
FROM
A as a
JOIN
B as B
on
a.a1 = b.a1
and
a.a2 = b.a2
JOIN
C as c
ON
b.b1 = c.b1
and
b.b2 = c.b2
Where fieldtime <= date/time
But it takes to much time: aftre 2 hours and half is still running.
Do you know how to increase the performance?
Thank
View 7 Replies
View Related
Dec 5, 2007
I have a query that joins two large partitioned tables and depending on the values in the where clause, I can get dramatically different performance results.
The first query completed in around 7s and has 47,000 logical reads.
select mo.monitor_id,
mo.site_id,
mo.testtime,
sum(mo.NumBytes),
sum(mo.DNSTime),
sum(mo.ConnectTime),
sum(mo.FirstByteTime),
sum(mo.ContentTime),
sum(mo.RelocTime)
from monitor_raw mr(nolock), monitor_object mo(nolock)
where mr.monitor_id in (5339, 5341, 5342, 943842, 943866)
and mr.testtime between 'Oct 31 2007 3:00:00:000PM' and 'Nov 30 2007 3:00:00:000PM'
and mo.returncode = 200
and mr.site_id in (101,102,105,109,110,112,115,117,119,122,126,151,132,139,129,135,121,138,143,142,159,148,128,171,176,177,178,111,113,116,118,120,127,133,131,130,174,179,185,205,200,202,203,204,210,211,208,209,212,213,216,199,214,224,225,229,230,232,235,241,245,247,250,254,261,267,264,265,266,268,269)
and mr.escalationlevel = 0
and mr.monitor_id = mo.monitor_id
and mr.testtime = mo.testtime
and mr.site_id = mo.site_id group by mo.monitor_id, mo.site_id, mo.testtime
The second query takes 188s to complete and has 1.8m logical reads. The only difference between the two is the value of the monitor_ids in the where clause.
select mo.monitor_id,
mo.site_id,
mo.testtime,
sum(mo.NumBytes),
sum(mo.DNSTime),
sum(mo.ConnectTime),
sum(mo.FirstByteTime),
sum(mo.ContentTime),
sum(mo.RelocTime)
from monitor_raw mr(nolock), monitor_object mo(nolock)
where mr.monitor_id in (152682, 5339, 5341, 5342, 268080)
and mr.testtime between 'Oct 31 2007 3:00:00:000PM' and 'Nov 30 2007 3:00:00:000PM'
and mo.returncode = 200
and mr.site_id in (101,102,105,109,110,112,115,117,119,122,126,151,132,139,129,135,121,138,143,142,159,148,128,171,176,177,178,111,113,116,118,120,127,133,131,130,174,179,185,205,200,202,203,204,210,211,208,209,212,213,216,199,214,224,225,229,230,232,235,241,245,247,250,254,261,267,264,265,266,268,269)
and mr.escalationlevel = 0
and mr.monitor_id = mo.monitor_id
and mr.testtime = mo.testtime
and mr.site_id = mo.site_id group by mo.monitor_id, mo.site_id, mo.testtime
The two tables have clustered indexes on monitor_id, testtime and site_id. Comparing the execution plan, I can see why there is such a difference in performance. The second query performs a clustered index seek on the monitor_object table starting at the lowest monitor_id, testtime & site_id through the highest monitor_id, testtime & site_id. The first query performs a clustered index seek where the monitor_id, testtime and site_id equals the same values from the monitor_raw table.
My question is, how can I force the second query to use the same execution plan as the first so that I can get better performance?
One possible workaround that I could use is to execute five individual queries, one for each monitor_id and then union the results together but this would require significant code changes to my stored procs.
Thanks,
Tim
View 5 Replies
View Related
Mar 19, 2002
every morning I have 7-10 identical messages in error log
1.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2.
Error: 15457, Severity: 0, State: 1
3.
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
It is standby server with custom log shipping and DTS transfering logins every 15 min.
What could cause this error ?
View 1 Replies
View Related
Feb 6, 2007
my supervisor is trying to help move our existing database in IBM U2 into SQL Sever he has a file/table that as 12001 columns, 1 column is the key300 columns are different field errors that can be checked true or falseand for each of those 300 columns there are three extra columns where extra information can be stored is there a size limitation when it comes to columns in a SQL table?what is an efficient way of doing this?create one giant table with 12000 columns?create 300 tables, each table associated with a error association? which will then need to be joined?any suggestions? comments? tips?
View 3 Replies
View Related
Aug 6, 2015
When I run below query I get 3 columns, but when I try to add table name ind.object_name (object_id) it's giving me an error "Ambiguous column name 'object_id".How do I add tables name with 3 columns?
SELECT ind.name, ic.index_id , ind.type_desc from sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
View 4 Replies
View Related
Aug 2, 2007
Hi,
i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case?
Thx
Frank
View 7 Replies
View Related
