5
votes
2answers
282 views

Index seek much slower with OR condition compared with separate SELECTs

Based on these questions and the answers given: SQL 2008 Server - performance loss possibly connected with a very large table Large table with historical data allocates too much of SQL Server 2008 ...
0
votes
2answers
63 views

View with left joins are very slow

I have a view based on join of 3 tables and and query on view is taking 10 mins to fetch the results. Can anybody help me in reducing the time taken to fetch results? VIEW CREATE view ...
3
votes
4answers
135 views

Are we a good fit for SQL Server Enterprise and table partitioning?

We currently have a production database that contains millions of records (14 million in the largest table). One function of the database is to provide time sheet information so a single table could ...
0
votes
3answers
98 views

Why is my select statement so slow?

SELECT name FROM sys.databases WHERE name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC') AND CASE WHEN state_desc = 'ONLINE' THEN ...
1
vote
1answer
42 views

Performance of checking for data in related tables

NB: this question is purely academic / to help improve my understanding of SQL Server performance. Given a master table which relates to one or more other tables, how would you determine the best ...
0
votes
2answers
80 views

Query Optimization required - Subquery using Inner Join

Could anybody advise how can I optimize this query below please? SELECT IT.[iInternalTransactionID] , IT.[iInternalAccountID] , C.[iContractID] , IT.[dtTransactionDate] AS TransactionDate , ...
0
votes
0answers
56 views

A working view now starts timing out

I have 2 identical databases (one larger, one smaller) with identical views. Recently a view on the smaller DB started timing out. Google searches seems to point to deadlocking of some sorts. If I ...
4
votes
2answers
111 views

Clustered Table Scan Because of “SELECT *”

I have a Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths: CREATE NONCLUSTERED INDEX [IX_Records_CustomerID] ON [dbo].[Records] ( ...
3
votes
1answer
76 views

Sargable Queries

Very simple question I hope, I'm currently having some scripts I inherited into my project reviewed by the database admins. This has never been done before apparently and lots of issues are being ...
1
vote
2answers
246 views

Improve query performance when selecting almost all rows with many “group by” columns

I have a table with 20 columns and about 600,000 records. The maximum row size is only around 100 bytes. The table is repopulated every few days, but the number of records remain about the same. For ...
-3
votes
2answers
94 views

how to solve slow running sub query TSQL

I have a problem with a slow running query. I have analysed the query with the sql server profiler but I can't seem to find a good solution. I did find why it is running slow. If ask the full ...
5
votes
2answers
137 views

An efficient way to compare two large data sets in SQL

Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations. The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and ...
1
vote
1answer
141 views

Understanding execution plans using TVPs

I have some slow running queries (8 seconds across 1M records grouped by months) and have been trying to make sense of the execution plans. We use around 10 TVPs to send in a set of filters that the ...
5
votes
2answers
245 views

Cannot tune database any further; what next?

We have a vendor-supplied application. It is under support and we are talking to their developers (we have their largest database size by an order of magnitude), but in the meantime, we have the ...
4
votes
1answer
115 views

Search, filter and group across all columns in a tall and wide dataset quickly [closed]

We have certain datasets which may span 80 to 300 fields, across 300,000 to 35 million rows. For each dataset, we want to produce a web based analysis tool (a web grid) which would allow our users ...
2
votes
1answer
126 views

Does SQL check each And condition if first condition fails? [duplicate]

For a query of below kind does sql process data for each And condition if first And condition fails? If Emp_ID is not 12 for a row then does it check condition of Emp_Name? So how SQL process this ...
0
votes
1answer
42 views

NTEXT,Indexing, and lob logical reads

We have an query that would benefit from a covering index but one of the columns that would be returned is a NTEXT data type. I know this can't be added to the index and I also know it would be good ...
4
votes
2answers
520 views

Why is my plan guide not being used?

We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ...
1
vote
1answer
171 views

Retrieve next queue item

I have a simple table in SQL Server 2012 which implements a processing queue. As data has been inserted the query to retrieve the next item has gone from <100ms to a constant 5-6secs. I would be ...
3
votes
2answers
141 views

How can I eliminate this costly index seek operation from my query?

I have two tables in Sql Server 2008 that look something like this: Transaction_Details: Dataset, Department, Charge_ID, Transaction_Type, dos_month, post_month, amount ...
2
votes
2answers
182 views

SQL server query is slow *without* including execution plan

I have just set up fresh installation of SQL Server 2012 on a new server and I'm experiencing very strange performance problems. Long story short, for some reason not including execution plan makes my ...
3
votes
1answer
79 views

How do I deal with query getting slower when a select parameter is not specified inline?

I faced this problem twice with SQL Azure and two different queries so far. Something like this: select top(100) * from SomeView WHERE AccountId='actual-guid-typed-in-here' runs super fast but ...
0
votes
3answers
124 views

Is table partitioning improving performance? Is it worth it?

I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
3
votes
1answer
68 views

Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. ...
0
votes
1answer
58 views

Take query results and insert into new table [closed]

So I have the following query: DECLARE @tbl TABLE(value int) *--The same function but with the number of the random numbers ;WITH Nbrs (n) AS ( SELECT 1 UNION ALL SELECT 1 + n FROM ...
4
votes
2answers
181 views

Query tuning to meet timeout settings [closed]

We have an web app with a 30 second time out on search screens. I've analyzed the original query and added a missing index for one of the query's 9 joins. Other things like expected and actual ...
3
votes
2answers
1k views

High CXPACKET and LATCH_EX waits

I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait ...
0
votes
2answers
348 views

How can I speed up this query on table with 65 million records

select hash_id, hash_value, substring(T.cri, 1, 7), substring(T.cri, 9, D2.Pos-9), substring(T.cri, D2.Pos + 1, D3.Pos - D2.Pos - 1), ...
0
votes
2answers
55 views

Which one is better way to insert data? [closed]

I have two cases case create proc createEmployee @firstName varchar(255), @lastName varchar(255) = null as insert into Employee_Tbl values (@firstName, @lastName) case create proc ...
4
votes
2answers
65 views

Spatial index not used

I have a table with a geometry column. For one record, there is only one Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting ...
-4
votes
2answers
75 views

Performance with IN clause

Does indexed data affect query performance? means If I passed value as 1,2,3,4... in ordered form in any IN clause, can it improve query performance? and what happen in case I pass value randomly ...
4
votes
1answer
94 views

Drastic performance difference in View Dependencies between SQL Server 2005 and 2008 R2

Background I am a fledgling DBA who was hired in the middle of a migration from SQL Server 2005 x64 to 2008 R2 x64. We also migrated from a local server to a colo virtual server, and moved some of ...
1
vote
1answer
131 views

Performance of dynamic SQL Server reporting queries?

I have to build an application that allows the end user to select what columns they want in their report. For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns ...
3
votes
2answers
431 views

Are there advantages to using temporary tables over derived tables in SQL Server?

I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why? I must do queries with large data (millions records) and I ...
0
votes
1answer
201 views

Improve Insert performance for 200 Million rows

How can the following insert statement be improved - which varies from 20mins on certain days to 70-140mins on most other days. SELECT DISTINCT od.SourceID, od.OrderID, od.OrderLineNo, ...
2
votes
2answers
83 views

Comparing Queries

In SQL Server, if you're comparing the statistics IO from two queries, how do you ensure that caching doesn't become a factor? For example (I'm making this up as I type, not a real example): SELECT ...
0
votes
1answer
109 views

T-SQL Optimizing a Join on TOP Value from another table

I've got a data warehouse that goes through a full refresh each night that can take about an hour to process 16 million rows/25gigs of data and we're looking for ways to reduce this time without going ...
2
votes
0answers
54 views

Single table row “sharding/versioning” to customize values

Introduction Hello everyone, I have done quite a bit of searching and I cannot find a solution for my specific issue. There are a few factors that complicate my situation, which I have outlined ...
2
votes
1answer
81 views

Getting each status change in a table

I have a table that holds status changes per item. The item can go in and out of a status multiple times. ItemTable (ItemId int, Status varchar(50), DateOfChange date) I need to pull the date of ...
0
votes
4answers
306 views

Can migration from MS Access to SQL Server or … make database faster? [closed]

A company asked me to migrate their database from MS Access to SQL Server or MySQL because they think that on those engines their database will run faster. But I wonder if their problem isn't because ...
3
votes
2answers
179 views

Should I add multiple secondary indexes to search

I have a search screen on my app where the users can use up to 4 parameters to search. I have written a stored procedure to facilitate the search. Select ID, FirstName, LastName, ...
-2
votes
1answer
98 views

Maximum dataset size for a well-performing SQL Server 2012? [closed]

We're planning a project using SQL Server 2012 for storing about a billion records over less than 50 fields. Assuming good hardware, is SQL Server up to that task? Roughly how many request/second can ...
4
votes
1answer
617 views

Optimising a query against sys.fn_dblog

Looking for assistance improving a query. This query is used to review the last N transactions in the LDF file and then add some useful information like the user, start time, query text, etc.. Here ...
0
votes
0answers
74 views

Query with large number of or'ed predicates on the same column?

At one place we need to query a table to determine whether any records exists that are in a subset of the same table. The columns that are queried are varchar columns containing file-paths, and we ...
1
vote
1answer
428 views

SQL Server performance for tables having large number of columns [closed]

I have a requirement where I need to create a table with about 600 columns. Out of 600 columns about 200 columns will be XML columns. Other column will be of other datatypes such as int, varchar, ...
1
vote
1answer
981 views

Some queries are very slow after index rebuild

After finding out almost all databases on my SQL Server had fragmentation over 40%, I decided to do an index rebuild on all tables using a fill factor of 80. After rebuilding all indexes, some ...
2
votes
2answers
150 views

What can be done to further enhance performance of Multiple Join and Aggregate Queries?

I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and ...
1
vote
4answers
212 views

Which of these queries is best for performance?

Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i ...
0
votes
2answers
235 views

Why would a long running query remain sleeping and with null wait_info

The following query was run on SQL Server 2008 R2 and took over 1 hour to run: select log_id, type, instance_id from ts_cache_log where log_id > 146400 order by log_id It produced 0 rows ...
4
votes
2answers
939 views

GROUP BY with MAX versus just MAX

I am a programmer, dealing with a big table which the following scheme: UpdateTime, PK, datetime, notnull Name, PK, char(14), notnull TheData, float There is a clustered index on Name, UpdateTime ...