A QUERY THAT RUN ON DB2 THAT HAVE MORE PERFORMANCE THAN SQL SERVER 2000
Apr 26, 2006
The execution time for this query on DB2 v8.0 DBMS one second but I execute it on SQL SERVER 2000 is around 55 second
so how i can incease the performance for SQL server
SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST = 5 ) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1
Note 1: value 5 exist in most of rows about ( 999999/1000000 ) from the table rows count
Note 2: the number of rows in each table around 15000000
Note 3: I used the same index structure for both DB2 and SQL server 2000
Note 4: I used some other feature in DB2 that increase the performance but I did not
found the alternative for it in SQL server 2000 :
a- cardinality varies at run time feature
b- include column in index instead of use compound index for
( ACC_KEY1 ,ACC_STATUS_LAST ) columns
Note 5 : Enable reverse scan for index
View 3 Replies
ADVERTISEMENT
Aug 1, 2007
Hi,
I'm having an issue with a query I'm running on Sql Server 2005. It's a semi-complex query involving an in-line table function and several left outer joins which are joined on to the results of the function call. Two of the left outer joins are then qualified in a where clause of the form where table.Col is not null; the idea is that the final result set contains data that has no match in those two tables.
The problem revolves around a where clause in the function and the last left outer join (ie, one of the ones qualified with where not null). When I alter the where clause of the function to further restrict the result set the function returns, the query times shoots up from 1 second to roughly 2-3 minutes. Note that the time the function takes to complete is not affected. The difference in time is purely down to what the query does with the results the function provides. Also note that the change to the where clause provides a subset of the original data; it does not add any more data (it actually restricts the original resultset by roughly 1000 rows).
I can bring the query speed back down again by removing the last left outer join - this join takes one of the columns from the function, and joins it to a small table - 924 rows. So it appears that this particular join is the cause of the issue, but only when using the resultset generated from the modified function query.
Now, as the thread title alludes, Sql Server 2000 and 2005 handle this differently, or appear to. When I execute this same query on a Sql 2000 machine, there's no apparent time differences, and the data that is returned is as expected. Does anyone have any suggestions as to what might be causing this and how I can fix it? I could simply return the larger resultset and use managed code to filter out the rows I don't want; however, I would like to get to the bottom of this, especially if it's going to effect future queries.
Cheers,
Chris
View 4 Replies
View Related
Jul 23, 2005
Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike
View 4 Replies
View Related
Jun 14, 2007
Good day,
Very recently my sql server started performing very poorly. Nothing has been installed or loaded on it to cause the poor response. What ever I do, from running a query to opening windows explorer response well, all very slow and very often query time out, ones that took a few second to run before.
The CPU usage and memory usage do not indicate anything specific. Is there some way I can pin point specifically what database or query or job is causing the problem.
Please can someone help me, this is VERY urgent, our production is suffering huge, and a reload is not an option as I can't get a backup of the databases as they also time out.
PLEASE HELP!!
View 1 Replies
View Related
Jul 23, 2005
Will the next release of SQL Server 2000 64bit sp provide performancecounter?MarcM
View 2 Replies
View Related
Jul 28, 2006
Hi
I have a database that stores all the data in one table that is only the data we are required to store hence it didnt require any other tables
the table has five colunms
Orderid, ordercost, orderdate, ordersystem, orderref
it is used in an web application that seraches for all the rest of the information using an orderid and displays the rest of the details the size of the table currently is about 123000 records that is increasing by 20000 every week.
i have an archive of the same data that has 7,666,000 records that are also going to be placed into the same database.
my question is is that a good idea of doing this?
are there any performance issues that i need to be aware of as currently the application runs quite quick?
View 15 Replies
View Related
Apr 11, 2006
I want to write a application monitering program to collect the SQLServer 2000 performance data,such as pages/sec, bytes total/sec, etc, BUT I don't know how to do it, In Oracle , there are the v$ views and DBA view , which I can findthe information I interested, the question is , is there a similar suitof view in SQL Server 2000 to provide the performance information ?Thank you very much, I will be mad by this question, for I have googledall the day , but in vain
View 1 Replies
View Related
Nov 15, 2007
Hi!!
In our SQL server is installed in Windows 2003 Standard edition. there about 30 clients using SQL server....
* How to improve SQL server performance ??
* How to improve Network traffic problem ??
* Should I use 2 Network Adapter Card???
Any idea please...
thanks...
View 4 Replies
View Related
Jul 23, 2005
What is the overhead of using extended stored procedures?I created a table with 500,000 rows.1) I ran a select on two columns and it runs in about 5 seconds.2) I ran a select on one column and called an UDF (it returns aconstant string) and it takes 10 seconds.3) I ran a select on one column and called a UDF that calls an extendedstored procedure that returns a string and it takes 65 seconds.I also tried running test 3 with 4 concurrent clients and each clienttakes about 120 seconds.
View 1 Replies
View Related
Jul 20, 2005
I changed from Access97 to AccessXP and I have immense performanceproblems.Details:- Access XP MDB with Jet 4.0 ( no ADP-Project )- Linked Tables to SQL-Server 2000 over ODBCI used the SQL Profile to watch the T-SQL-Command which Access ( whocreates the commands?) creates and noticed:1) some Jet-SQL commands with JOINS and Where-Statements aretranslated very well, using sp_prepexe and sp_execute, including thesimilar SQL-Statement as in JET.2) other Jet-SQL commands with JOINS and Where-Statements aretranslated very bad, because the Join wasn´t sent as a join, Accesscollects the data of the individual tables seperately.Access sends much to much data over the network, it is a disaster!3) in Access97 the same command was interpreted wellCould it be possible the Access uses a wrong protocol-stack, perhapsJet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server orJet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead ofJet to ODBC and ODBC direct to SQL-ServerDoes anyone knows anything about:- Command-Interpreter of JetODBC, Parameters, how to influence thecommand-interpreter- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server applicationThanks , Andreas
View 6 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
May 14, 2001
Hi,
I am now facing a problem related to the linked server. I created the connection between server A and B as linked servers. When I execute the following SQL statement on server A,
select * from B.database1.dbo.tableA where id ='12345'
I can get the results within couple of seconds. But the similar query would take several minutes if I switch the server name in the query from B to A and
run it on server B! The tables on server A and B actually have the same sizes and the same indexes.
Do you have any clues and suggestions on this issue?
Thanks in advance.
Keith
View 1 Replies
View Related
Oct 17, 2007
Hi,
I am having a table with 40 columns and it contains 4 million records. I got the results for one year in 40 secs. After tuning, it is retuning in 24 secs( what i have done is i created index on order by fields).
Can you please suggest me in which way I can increase the performance.
Note: I am using only one table with Primary Key.
Thanks
Dinesh
View 7 Replies
View Related
Sep 28, 2007
Hi guys,
I have a performance related question about the DTS package in sqlserver 2000 which i have developed
We have developed a DTS package which will migrate a view 'ATTRITION' from Sqlserver 2000 to an Oracle database.The design of the package is as follows
First step: It checks for the existance of the table 'ATTRITION' in oracle database, if table 'ATTRITION' is not there it will create a table called 'ATTRITION' in the oracle db.If the table 'ATTRITION' is already present in the oracle db,then the table is truncated.
Second step: The view 'ATTRITION' is migrated to Oracle table 'ATTRITION'.
For the migration, i have used a connection object which connects to sqlserver 2000 and for oracle connection i have used another connection object 'Microsoft ODBC driver for oracle' and i have joined both the connection objects with 'Transform data task' task which maps one to one from sqlserver 2000 where view 'ATTRITION' exists with oracle database where Table 'ATTRITION' exists.
Roughly i have around 65000 rows in 'ATTRITION' view of sqlserver 2000 which needs to be migrated.When im running the package on my system it takes around 4 minutes to migrate all the rows but when im running it on the server it takes a lot lot of time more than 1 hour.
The view definition im using has more than 10 tables joined together.But if its a problem of query used in the view,and if i run the view seperately it quickly displays the data hardly takes 1 minute. and even if i run the package on my local pc it doenst take much time.Now my confusion is why its taking soo much time on server.If i create a indexed view then will it solve my problem.Please suggest...
Thanks in advance
REgards
Arvind L
View 5 Replies
View Related
Jul 27, 2015
For example in a Select Statement we have many tables and we have Where Clause with many conditions with AND operations. Do the SQL SERVER would apply the Where clause after all fetch or can dynamically decide about to include the related Tables from Select Statement Orderly with respect to where clause predicates? (SQL SERVER would not fetch data of those tables for its Select, where the AND condition in Where clause fails or by logic would be fruitless/not-related.)
View 5 Replies
View Related
Jun 28, 2006
Executing a select query with left outer joins etc takes .53 seconds to execute on sqlce 2.0.
Same query on sql 2005 mobile ed. takes 11 minutes .
on database having same data.
Sample query
SELECT routes.location,routes.equipment_type, routes.contract_type,
routes.maintenance_interval,routes.bank_description,routes.Unit_Des,
routes.Unit_no,max(task_last_completed.date_completed)as date1,min(case when
task_last_completed.due_date is NULL then getdate()-1 else due_date end) as
due_date FROM routes left outer join tasks on tasks.model = routes.model and
tasks.eqtyp = routes.equipment_type inner join task_by_contract_type on
tasks.task_id = task_by_contract_type.task_id and
task_by_contract_type.contract_type = routes.contract_type and
task_by_contract_type.model = routes.model left outer join
task_last_completed on routes.unit_no = task_last_completed.equipment_Id and
tasks.task_Id = task_last_completed.task_Id WHERE routes.location LIKE
'S153825-01%' group by
routes.location,routes.equipment_type,routes.contract_type,routes.maintenanc
e_interval,routes.bank_description,routes.unit_des,routes.unit_no ORDER BY
routes.location, routes.bank_description, routes.Unit_Des
WHY????????????????
View 4 Replies
View Related
Jul 20, 2005
How does the preformace of SQL Server depend on the number of databases?Theoretical maximum number of databases on one MS SQL Server is 32000. Whatis the maximum number of databases in real life?TPa
View 1 Replies
View Related
Jul 20, 2005
We are running SQL Server in 6.5 compatability mode. We have some queriesthat are supported in 2000, that is the reason for the 6.5 version. What,if any, would be gained in performance by switching to 2000.
View 3 Replies
View Related
Jun 22, 2007
I have sql 2000 running with a client database that is about 200 people per day. A VB front end runs it. I have some problems with performance. Would upgrading to Sql 2005 improve my database performance?
View 5 Replies
View Related
Nov 29, 2007
if it is possible to run a distributed query against 2000 from 2005, what would the OPENDATASOURCE parameters look like? I'd like to be able to pivot without copying my older 2000 db to 2005 or using linked servers..
For reference, here's an example of a distrib query that reads excel...
ie SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: estxltest.xls;Extended Properties=Excel 8.0')...[Customers$]
View 1 Replies
View Related
Jan 16, 2008
Hello
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
View 5 Replies
View Related
Mar 26, 2001
I am hoping that an extremely experienced SQL Server DBA can assist in this critical matter.
We are in a 6.5 to 2000 upgrade process. Our current app runs in 6.5 with acceptable performance times. However, in our testing 2000 has performed miserably (4-10 times longer for identical processes with identical hardware configurations.) The SQL Server and Windows 2000 server has been configured, according to all documented books by MSPress, for optimal performance. We have reviewed all code that Profiler has helped us identify as potiental bottlenecks in the duration of our largest nightly process. The identified code has been scrutinized by both the development team and dba team using all known techniques in determining indexing, better logical and physical reads, optimal query executation plans and ect.
We are perplexed by having found out that identical, optimalized, indexed code runs the same or in most cases worse in 2000 then in 6.5. Our largest performing benchmark sp's has shown that a process in 2000 runs 2-3 times slower than 6.5.
Are there any undocumented tuning, optimizing or 6.5 to 2000 upgrade issues that can be shared as to why 2000 appears to run slower then 6.5? At this point, it would be greatly appreciated if a highly expereinced DBA can provide ANY insight. If no suggestion can be provided, perhaps a contact or names of VERY skilled SQL DBA's that can be contacted in assisting this problem, whether it is a third party or someone from MS.
TIA
View 9 Replies
View Related
Oct 18, 2007
Hi all,
We are in the process of upgrading a sql 2000 database over to 2005 and have noticed some substancial performance drops with scalar udfs in 2005.
I have already read the following post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=491984&SiteID=1
and recognise that udfs are not the most performant option in the first place, but was surprised how much slower these have become on 2005.
Has anyone else had this sort of issue, we really don't want to go away from the udf's but would like to know if there is a design issue within a udf that might be causing this (or even a usage issue). What I am getting as is: Is there certain types of queries, or keywords that should be avoided in udfs on 2005?
A simple example we have is a udf that returns an exchange rate stored in the db, this has parameters of "from currency", "to currency", and date.
SET @ret = ( SELECT TOP 1 TELE_TRANSFER_RATE
FROM dbo.EXCHANGE_RATES
WHERE EXCH_CURRENCY_NO = @from
AND CURRENCY_NO = @to
AND RATE_DATE <= @date
ORDER BY RATE_DATE DESC )
RETURN @ret
And then this is called from a script that returns financials, standard select statement, with udf call in select clause.
Any comments?
Thanks in advance.
Clint Colefax
View 6 Replies
View Related
Feb 10, 2008
Hi guys
We are in the process of moving from SQL Server 2000 to 2005. In this process in general I have noticed that performance is better as a result of the move but in a couple of specific cases performance is about 10 time worse as a result of the move and i am wondering if anyone can tell me why.
1) Should I be noticing that calling functions from within a where clause are slower in 2005.
2) Has the and/or logic processing been changed between the different versions.
3) Why does this segment of code run really slow in 2005 but really fast in 2000 (note, i know that its not nice looking but it is pre-existing code from before we came on board and there are more examples of these so its a bit of a change to go through and fix it all up to what it should be but i need to know why before i can move on and as i said i know its not nice and one should expect it to be slow but i specially need to know why it would run fine in 2000 and not on 2005):
.....
AND (Deleted = 0)
AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)
OR (ProgressPointId = 32)
AND (Deleted = 0)
AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)
OR (ProgressPointId = 30)
AND (Deleted = 0)
AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)
....
Thanks for your help.
Anthony
View 5 Replies
View Related
May 15, 2008
I was hoping I wouldn't be another poster with performance issues after migrating to SQl 2005 from SQL 2000 but here I am.
I am in the process of testing out our databases on Sql Server 2005 for migration from SQL Server 2000 and there are certain portions of code that have been affected negatively. I have read thru many of the posts here and have tried out most of the recommendations. I will start out with things I've done and then provide the actual SQL.
1) I have rebuilt all indexes ( using the DBCC REINDEX using the table option).
2) Updated the db engine to latest hot fix (build 3239) that addresses speed related fixes.
3) I also ran sp_createstats using the 'fullscan' option to create stats on all columns of all tables (minus indexed columns)
4) Since nothing seemed to work, I even ran UPDATE STATICS with FULL SCAN on all tables even though I did not need it as the REBUILD woudl have created stats. But I was willing to try anything.
I have confirmed that the execution plans are different even though the data on both sql 2000 and sql 2005 are identical (i put a copy on 2005). The plans themselves are huge as the queries are huge. Here is the query.
SELECT InterimView.* ,TestView.*
FROM View_LabDataExport_TestFormData_55 TestView
RIGHT OUTER JOIN ( SELECT ReqView.*, CDView.*
FROM View_LabDataExport_FormData_55 ReqView
LEFT OUTER JOIN View_LabDataExport_FormData_CD_55 CDView
ON ( CDView.DB_SubjectID_CD = ReqView.DB_SUbjectID )
) InterimView
ON ( InterimView.DB_FormID = TestView.DB_FormID_T AND
InterimView.DB_LabSampleID = TestView.DB_LabSampleID_T )
The above query takes abotu 8 secs to run on 2000 and about 1 minute to run on 2005. This is for a small dataset and on larger datasets this is only going to more pronounced ( as confirmed by other teams that have already migrated in my company). Another point worth mentioning might be if I remove the TestView.* from the select list, it works in 5 to 6 seconds. Is there an issue with Sql 2005 and a large number of columns or anything of that sort? On 2000, the time remains the same , about 8 seconds if I remove this from the select list.
Here is the statistics ion on 2005
(21234 row(s) affected)
Table 'Worktable'. Scan count 75490, logical reads 3676867, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabTestToReportPanel'. Scan count 476, logical reads 1524, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabReportPanel'. Scan count 0, logical reads 260, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DiscreteValue'. Scan count 1, logical reads 176106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabReleasedSampleTest'. Scan count 1, logical reads 2078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabSample'. Scan count 1360, logical reads 18567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Form'. Scan count 2302, logical reads 8225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabTest'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabSampleDef'. Scan count 1, logical reads 10530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabArea'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Lab'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Location'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Study'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Item'. Scan count 1335, logical reads 32940, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ObjectState'. Scan count 1, logical reads 10972, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Object'. Scan count 0, logical reads 20674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Subject'. Scan count 0, logical reads 3293, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FormDef'. Scan count 2, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PrintedLabSampleLabel'. Scan count 0, logical reads 13144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PrintedForm'. Scan count 0, logical reads 4219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StudySite'. Scan count 0, logical reads 2756, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StudyEvent'. Scan count 18, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StudyEventDef'. Scan count 0, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Here is the statistics ion on 2000
Table 'LabTestToReportPanel'. Scan count 2123, logical reads 4820, physical reads 44, read-ahead reads 0.
Table 'LabReportPanel'. Scan count 130, logical reads 260, physical reads 0, read-ahead reads 0.
Table 'DiscreteValue'. Scan count 103914, logical reads 208214, physical reads 0, read-ahead reads 0.
Table 'Location'. Scan count 19031, logical reads 38062, physical reads 2, read-ahead reads 0.
Table 'Lab'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.
Table 'LabArea'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.
Table 'LabSampleDef'. Scan count 24670, logical reads 49340, physical reads 0, read-ahead reads 0.
Table 'LabTest'. Scan count 19406, logical reads 39575, physical reads 0, read-ahead reads 0.
Table 'LabReleasedSampleTest'. Scan count 4289, logical reads 73865, physical reads 1014, read-ahead reads 24.
Table 'Study'. Scan count 4291, logical reads 8582, physical reads 0, read-ahead reads 0.
Table 'LabSample'. Scan count 5647, logical reads 31382, physical reads 308, read-ahead reads 4.
Table 'Form'. Scan count 4291, logical reads 9272, physical reads 2, read-ahead reads 10.
Table 'PrintedLabSampleLabel'. Scan count 4289, logical reads 17097, physical reads 114, read-ahead reads 308.
Table 'ObjectState'. Scan count 6860, logical reads 13760, physical reads 1, read-ahead reads 0.
Table 'Object'. Scan count 6860, logical reads 23559, physical reads 90, read-ahead reads 701.
Table 'PrintedForm'. Scan count 1375, logical reads 4505, physical reads 40, read-ahead reads 16.
Table 'StudySite'. Scan count 1378, logical reads 2756, physical reads 4, read-ahead reads 0.
Table 'Subject'. Scan count 1599, logical reads 3332, physical reads 2, read-ahead reads 0.
Table 'StudyEvent'. Scan count 18, logical reads 52, physical reads 0, read-ahead reads 0.
Table 'StudyEventDef'. Scan count 18, logical reads 54, physical reads 0, read-ahead reads 2.
Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 69, physical reads 0, read-ahead reads 23.
Table 'FormDef'. Scan count 2, logical reads 78, physical reads 1, read-ahead reads 4.
Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 308, physical reads 1, read-ahead reads 306.
Table 'Item'. Scan count 1335, logical reads 36510, physical reads 140, read-ahead reads 1047.
(21234 row(s) affected)
(147 row(s) affected)
One difference between the two is the work table that 2005 creates versus 2000. I can attach the plans but they are huge. I will attach it if you ask.
What I was looking for was suggestions on what I could do short of rewriting code or any suggestions in general.
Thanks
View 20 Replies
View Related
Oct 4, 2007
Hi,
I am using SQL SERVER 2000.
I need a procedure/ method where i need to display the output value of a particular query.
As I wrote the following procedure
CREATE proc proc1as beginDeclare @str nvarchar(100)set @str = 'select top 5 * from CT_TM_EmployeeMaster'print @strend
the output is giving "select top 5 * from CT_TM_EmployeeMaster"
I need the output of the above query. What should i do?
Thanks & Regards,
JaiShankar
View 4 Replies
View Related
Nov 11, 2005
Dear all,I have a database with 3 tables, one is named Enrollment, and the other Attendance. What I am trying to accomplish here is to get all the Student ID from the Enrollment table which matches a given class ID and insert all of them into the Attendance table for a given day.I did this with the SQL query below :-
INSERT INTO Attendance (ClassID, Date, StudentID, Status) VALUES (1, '10-4-2005', (Select t.StudentID FROM Student t WHERE t.ClassID=1), 1)Trouble here is I get the error :
Subqueries are not allowed in this context. Only scalar expressions are allowed.I understand waht is going on here, that it returns more than 1 result. So my question is, are there any other ways to accomplish what I am trying to do? Hope you all can help me with this.Thanks all in advance,-Victor-
View 4 Replies
View Related
Feb 1, 2007
This query executes (instantly) in SQL Server 2000 without SP4. With SP 4, it hangs - goes over 5 mins and does nothing. If we make the following change to the query: (replace the variables with actual values)
...
where Links.Matter_ID = 5872
AND Links.Entity_ID = 1
Order By Contacts.ContactID
...
It executes almost instantly in SP4 as well.
Looks like a SP4 issue, any ideas on how we could fix this?
t-sql follows:
Declare @MatterID bigint
Declare @EntityID int
Declare @ReturnDetails int
Declare @RETVAL VarChar(8000)
Declare @EntityDetails VarChar(1000)
Set @MatterID = 5875
Set @EntityID = 1
Set @ReturnDetails = 0
DECLARE Entity_Cursor CURSOR FOR
Select CASE WHEN Contacts.Job_Title IS Null Then '' Else Contacts.Job_Title + ' ' End + CASE WHEN Contacts.First_Name IS Null Then '' Else Contacts.First_Name + ' ' End + CASE WHEN Contacts.Middle_Name IS Null Then '' Else Contacts.Middle_Name + ' ' End
+ CASE WHEN Contacts.Last_Name IS Null Then '' Else Contacts.Last_Name End EntityDetails
From Links
INNER JOIN Contacts
On Links.Contact_ID = Contacts.ContactID
where Links.Matter_ID = @MatterID
AND Links.Entity_ID = @EntityID
Order By Contacts.ContactID
OPEN Entity_Cursor
FETCH NEXT FROM Entity_Cursor INTO @EntityDetails
Select @RETVAL = ''
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'test'
/* If @ReturnDetails = 1
Select @RETVAL = @RETVAL + @EntityDetails + '
'
Else
Select @RETVAL = @RETVAL + @EntityDetails + ', '
FETCH NEXT FROM Entity_Cursor INTO @EntityDetails */
END
CLOSE Entity_Cursor
DEALLOCATE Entity_Cursor
View 5 Replies
View Related
Nov 21, 2007
Hi All,
How to implement a hierarchical query in SQL Server 2000.
Example ; I have an Oracle Query as below
SELECT LEVEL,employee_id, manager_id, first_name, last_name
FROM employee
START WITH employee_id = 1
CONNECT BY prior employee_id = manager_id;
I need to get the equivalent of this query in SQL Server 2000 .
In SQL Server 2005 i can achieve this using COMMON TABLE EXPRESSION .
Is there any way to implement this in SQL Server 2000
View 6 Replies
View Related
Sep 15, 2006
I have two tables in an inner join. I'm detailing the tables with some of their fields below. These tables are in a database I'm creating to manage backup tapes. Most importantly, this database will inform me when backup tapes which have already been used can be recycled (e.g. after all the jobs on the tape are over 28 days old). I want to write something which will look at each tape in turn and, if all related backup jobs on that tape are aged, the tape status will be changed from Active to Spare.
Tapes
--TapeNo
--Status (Spare / Assigned)
Jobs
--JobNo
--Name
--Description
--TapeNo
--AgedJob (BIT field indicating whether or not the job has aged)
Each tape can have 0, 1 or many jobs and each job can be on more than one tape.
Anyway, I have the tables and relationsips set up and they're ok. Again, what I'm struggling with is how I take each tape and look at all its jobs and, if all have aged, change the Status for the tape to Spare. I'm using SQL Server 2000 (Access 2003 as front end) and am pretty new to SQL. I was thinking this could be done by using some kind of update query and subquery, but I'm stumped. Could someone please help ?
--
Paul Anderson
View 3 Replies
View Related
Jan 5, 2006
Hello.After searching Microsoft's website for hours and confusing myself morethan I was before I started...Can anyone explain to me what the current state of affairs is for SQL2000 licensing?In particular with a server that has multiple processors and multipleinstances of SQL Server 2000 running - for both Standard and EnterpriseEditions of SQL Server 2000.Please help!Regards,Taz
View 1 Replies
View Related
Jul 20, 2005
I am struggling rewriting my query from MS Access' IIF, Then to SQLServers TSQL language. I am hoping some one can give me someguidance. I believe I have the first portion of the query correct butdo believe this requires a "NESTED" argument. This is where I amlost.My Original MS ACCESS Query reads--SELECT DISTINCTROW REGION_TRAFIC.*,IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],IIf([Carrier]="VENDOR1",[DOMESTIC LDRATES]![INTRA_VENDOR1])))),IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],IIf([Carrier]="VENDOR1",[DOMESTIC LDRATES]![INTER_VENDOR1]))))) AS CPM,[CPM]*[MOU] AS COSTINTO INTRALATA_LDFROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ONREGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEYWHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))ORDER BY REGION_TRAFIC.[TERM LATA] DESC;I have tried to re-write this in SQL SERVER as --SELET DISTINCT REGION TRAFIC.*,CASEWHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LDRATES].INTRA_VENDOR4WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LDRATES].INTRA_VENDOR3WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LDRATES].INTRA_VENDOR2WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LDRATES].INTRA_VENDOR1ELSEWHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LDRATES].INTER_VENDOR4WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LDRATES].INTER_VENDOR3WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LDRATES].INTER_VENDOR2WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LDRATES].INTER_VENDOR1ENDAS CPMCPM*MOU AS COSTINTO INTRALATA_LDFROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ONREGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEYWHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))ORDER BY REGION_TRAFIC.[TERM LATA] DESCMy challenge is the Case portion of the query and the nesting! I amnot sure if I have the correct syntax or even chose the correctargument for my purpose.Any guidance is appreciated.
View 9 Replies
View Related
Sep 12, 2006
When you are inserting/altering a table and you expect values to be added, should you see the number of rows affected at the bottom of the query screen as the time goes by or not?
thx,
Kat
View 1 Replies
View Related
