Increase Performance In Query With Big Tables (milions Of Records)
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
ADVERTISEMENT
Apr 1, 2001
Hi,
I created a view on two huge tables. I tried to run a simple SELECT statement on this view and it took me several hours to obtain the result. How can I improve the performance of a view? The view should make use of the indexes built in both table, am I right? Thanks.
View 1 Replies
View Related
Jun 29, 2000
"Cursor provide row-by-row level processing and it will store the result sets in 'TEMPDB' database".
(Because of this) or (By using Cursor in Triggers or Stored Procedures) the performance will increase or performance will come down?. I am thankful if I get a good reason for this?
Srinivasan
View 3 Replies
View Related
Nov 3, 2006
Hello again,
I'll just throw my question: how could I increase SSIS-performance?
I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.
Any ideas?
Greets,
Tom
View 2 Replies
View Related
Sep 25, 1998
Hi,
I am writing an ASP based application that creates a dynamic querry and then
executes it and displays results. I was thinking about writing a stored procedure to increase performance. How much can the SP help me boost querry responce time ???
Thanks for your time,
Robert
View 1 Replies
View Related
May 28, 2008
Hi,
Can any one give me an idea how can i increase performance of the stored procedure.
In SP many temporary tables are used.
Also i need a information from any one you that is there any tool to find out the performance of a query or SP etc.
Thanks
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
View 4 Replies
View Related
Aug 13, 2015
WHILE (@MyLoop3 > 0)
BEGIN
SELECT Top 1 @UploadId = UploadId,@FirstName = (CASE WHEN FirstName = '' THEN @Update ELSE FirstName END),
@LastName = (CASE WHEN LastName = '' THEN @Update ELSE LastName END),
@Claim = (CASE WHEN Claim = '' THEN @Update ELSE Claim END),
@Insurer = (CASE WHEN Insurer = '' THEN @Update ELSE Insurer END),
@InsurerBranch = (CASE WHEN InsurerBranch = '' THEN @Update ELSE InsurerBranch END),
[Code] .....
View 3 Replies
View Related
Mar 5, 2008
Dear all,
I'm using SQL Server 2005 Standard Edetion.
I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags)
The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records
Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc
Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes.
So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I€™m using 5 conditions, I can€™t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:
CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))
CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))
INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC
INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType
FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID
WHERE RecordedCalls.ID <= '9369907'
AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))
AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)
AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')
AND RecordedCalls.ServerID NOT IN('2')
AND RecordedCalls.AgentID NOT IN('1000010000')
AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))
AND RecordedCalls.IsDeleted='false'
GROUP BY RecordedCalls.CallType
SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')
WHEN 'Arabic' THEN #tempLookups.NameA
ELSE #tempLookups.NameE
END AS CallsType FROM
#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code
DROP TABLE #tempLookups
DROP TABLE #tempTable
Thanks all,
Tayseer
Any suggestions how to create efficient indexes??!!
View 2 Replies
View Related
May 24, 2007
No transaction log involved, only the table itself.
Use sp_spaceused "table_name" to check the space used.
It seems the table size actually increased from the beginning to the middle of deletion, at the end of deletion, its size decreased.
Recovery mode set to be simple, autoshrink turned on.
The tables tested are about 50MB ~ several GB in size, all have the same behavior. The size increased about 5%~10%.
Since the deletion is called from another software, I want to know if it is possible for SQL Server to have this behavior or it is absolutely the 3rd party software's issue
Thanks!
View 2 Replies
View Related
Aug 2, 2007
When I am executing a query that uses a mathematical function on values from 2 tables the query takes much longer than the same query that uses values from 1 table, even though the join remains the same.
Why is this happening?
Is there a way to bypass this problem?
Long query ( values from 2 tables ) :
SELECT
MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = ‘test’
Short query ( values from 1 table ) :
SELECT
MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = ‘test’
Long query execution plan:
Execution Tree
--------------
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( ( sign ( [attribute].[keyValue]--2027587559 ) *sign ( [attribute].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
|--Nested Loops ( Inner Join )
|--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=’test’ ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Table Spool
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )
Short query execution plan:
Execution Tree
--------------
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( [partialagg1005] ) ) )
|--Nested Loops ( Inner Join )
|--Stream Aggregate ( DEFINE: ( [partialagg1005]=MAX ( ( sign ( [data].[keyValue]--2027587559 ) *sign ( [data].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
| |--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=’test’ ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )
View 1 Replies
View Related
Aug 31, 2014
SQL query to understand the names of all the available tables , number of records in these tables and size of these tables?
View 4 Replies
View Related
Mar 6, 2008
I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID.
I need to find out which ones are in the second and third table that do not show up in the first one,
which ones are in the first and third but not in the second,
and which ones are in the first and second but not in the third.
I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this.
Here is an example:
create table TBL1(PID int, info1 varchar(10) )
Create table TBL2(TID int,PID int)
Create table TBL3(XID int,PID int)
insert into TBL1
select '1','Someone' union all
select '2','Will ' union all
select '4','Have' union all
select '7','An' union all
select '8','Answer' union all
select '9','ForMe'
insert into TBL2
select '1','1' union all
select '2','1' union all
select '3','8' union all
select '4','2' union all
select '5','3' union all
select '6','3' union all
select '7','5' union all
select '8','9'
insert into TBL3
select '1','10' union all
select '2','10' union all
select '3','8' union all
select '4','6' union all
select '5','7' union all
select '6','3' union all
select '7','5' union all
select '8','9'
I need to find the PID and the table it is missing from. So the results should look like:
PID
MISSING FROM
1
TBL3
2
TBL3
3
TBL1
4
TBL2
4
TBL3
5
TBL1
6
TBL1
6
TBL2
7
TBL2
10
TBL1
10
TBL2
Thanks all.
View 5 Replies
View Related
Apr 11, 2007
Hi all,
I tried to change the size of a field of my table using the query :-
ALTER TABLE test MODIFY id varchar(50)
Initially the size of id was set to 30 .Is there any other way or any error in my query.Please help me soon.
joshymraj
View 2 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Jul 9, 2007
Hi,
SQLSERVER 2005 keeps throwing assertation error and generating crashdumps.
I want to isolate the cause, however, the query is too long (>2kb) to fit in a crash log.
Is it possible to increase the size allocated to showing the query in the crash dump, or get the full text of that query causing the crash?
View 1 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Apr 22, 2004
Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
View 2 Replies
View Related
Jan 18, 2008
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.
;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
Thanks in advance.
View 4 Replies
View Related
Sep 13, 2004
This question is regarding a "helper app" I'm building to go along with my ASP.NET appplication. It will be inserting/updating records in the database as a nightly process. It is a Windows application built in VB.net.
I have a table which should always only have one of each type of record in it. This table on average will have between 100k and 500k records.
Which operation would be faster and less strain on the server?
a. Use a "if exists" and see if a record of this type already exists, if it does, update it, if not, insert the new one.
b. Unconditionally issue a delete for the record I'm about to insert, then insert the new one.
c. Create a trigger that will delete the old record if a new one is inserted?
Thanks!
View 1 Replies
View Related
Jul 20, 2005
I have a SP that I use to insert records in a table. This SP is calledhundreds of times per minute.Most inserts complete very fast. And the profiler data is as follows:CPU: 0Reads: 10Writes: 1Duration: varies from 1 to 30But once in a while the insert SP seems to stall and takes a very longtime. Here's the info returned by profiles in this case:CPU: 0Reads: 10Writes: 1Duration: can vary from 6000 to 60000Note that the CPU, reads, writes remain the same. But the duration ofthe SP increases. What could be the reason for this?? The SPeventually completes in all cases - its just that they seem to take avery long time sometimes??What areas should I investigate??Thanks in advance,DK
View 8 Replies
View Related
Dec 12, 2007
Hi
We are using the SQL Server 2005 Full Text Service. The data is not huge, but the kind of data is that each record is small and there are a large number of records. There are 35 million records now with 11 GB of data and about 1.6 GB of FT catalog on the table. This is expected to grow to at least 10 times the size of this data. The issue is with FTS taking a long time to return results when the number of hits (rows) getting returned from FTS is large for some searches, it takes a very long time. With the same data & catalog, those full text queries for less common words return timely. The nature of the problem doesnt allow us to only have top results. We need all the results. So it’s not about the size of data but the number of results getting returned from FT. (As the catalog is inverted). The machine is dual processor with 4 GB RAM.
I am considering splitting the table and hence the catalog and using multiple servers to do full text searches in smaller catalogs. Is there any other way this issue can be solved ?
If splitting is the only way, can you give me an idea as to what is a statistical/standard limit to the number of search results/cataog size as which FTS gives good results
Thanks in advance
View 1 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
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
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
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
