4
votes
2answers
146 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 ...
2
votes
1answer
361 views

SQL Server 2005 - Query optimization for fetching large number of rows from table with 750 million rows

Brief on application: This is audio fingerprinting application, being developed in Java with Microsoft SQL Server 2005 database. I have one application to create fingerprints of original songs and ...
1
vote
1answer
239 views

Retrieving data speed tweaks SQL Server 2005

I have a database in Microsoft SQL Server 2005. I have table with 3 columns, namely HASHKEY (BIGINT), NOTE_ID (INT) and TIME_OFFSET (INT). Columns HASHKEY has 19 digit BIGINT values, which can ...
2
votes
2answers
169 views

Improve CTE performance

is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER? DECLARE @File_Name VARCHAR(8000), @Disk ...
2
votes
4answers
1k views

How can I query data from a linked server, and pass it parameters to filter by?

I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned. The basic syntax looks something like this: INSERT INTO #tmpTable (Id, ...
2
votes
3answers
295 views

Why is SQL running the same query longer from another connection? [closed]

Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely? Dont jump to any conclusion just yet, let me explain what I mean... Here is the setup: A Windows ...
2
votes
3answers
220 views

selecting multiple instances of a record based on record lifespan over years

I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a ...
1
vote
1answer
137 views

EXISTS and sub-query cost

i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns? 1) SELECT id FROM Parent p ...
3
votes
2answers
216 views

which one is more efficient query?

I have a table called STUDENT +-----------+-----------+-----------+---------------+ | StudentID | FirstName | LastName | EnrollmenDate | +-----------+-----------+-----------+---------------+ | ...
-3
votes
1answer
544 views

Single query to copy data from 3 tables into a single empty table

In an interview today, I was asked if I could write a single query to copy data from 3 source tables to an empty destination table. I started saying "I will use a temp table or table variables", ...
1
vote
1answer
776 views

Index Strategies on Text or NVARCHAR(MAX) Fields

I have the following query (simplified for question) I'm trying to speed up for a read only DB ... SELECT [sysid] ,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101) ,[From]=[from_addr] ...
6
votes
3answers
271 views

How can I identify when to create a new table to hold data that can be obtained from a query?

We have a payment table, and agents get commission on payments. Commission is based on a few different factors, such as how long it took to get the payment, so there is some calculations involved when ...
12
votes
3answers
2k views

Are Views optimized when I add a WHERE clause to them?

Does it make a difference if you filter a View inside or outside the View? For example, is there any difference between these two queries? SELECT Id FROM MyTable WHERE SomeColumn = 1 Or SELECT Id ...
3
votes
2answers
2k views

Copy huge amount from 1 table to other

I have two tables with same structure I need to copy over all tables from table A to table B, the problem is that some records from table A already exist in table B so that made the Import Fail. So I ...
9
votes
3answers
3k views

Why is an aggregate query significantly faster with a GROUP BY clause than without one?

I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one. For example, this query takes almost 10 seconds to run SELECT MIN(CreatedDate) FROM MyTable WHERE ...
8
votes
1answer
1k views

Monumental difference in execution time between queries when using RECOMPILE query hint

I have two almost identical queries running on the same SQL Server 2005 instance: The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application ...
5
votes
1answer
3k views

Why does a query run slower in a Stored Procedure than in the Query window?

I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure? Here's what my query looks ...
2
votes
1answer
244 views

Query tuning - SQL Server

One of our developers are trying to run the below query on a development server, which involves pulling data from a linked server, production. The query ran for more than 14 hours before it was ...