|
Hi,
i have a SQL database select Query which brings 60 columns from 10 tables via join. i need to fetch 300000(3 Lacs) rows from database. It takes 18-19 seconds to fetch 300000 rows. I want to decrease this execution time to 10-12 secs.
I used "DATABASE ENGINE TUNING ADVISER of SQL SERVER 2008 R2, it suggested me 2 create some Indexes and statistics. I have created all this, but could not improved the performance of my query.
can anybody suggest me right solution ???
This my query-
select
i.IssueId, i.IssueNumber, i.TeamId, t.Name AS TeamName, i.IssueStatus, p1.TextValue AS StatusText, p1.SortNumber AS StatusOrder, i.Substatus,
p2.TextValue AS SubstatusText, p2.SortNumber AS SubstatusOrder, i.CreatedBy, cc.FullName AS CreatedByFullName, i.CreatedAt, i.UpdatedBy,
cu.FullName AS UpdatedByFullName, i.UpdatedAt, i.ReportedBy, cReported.FullName AS ReportedByFullName, i.ReportedAt, i.Title, i.Description, i.Severity,
pSeverity.TextValue AS SeverityText, i.Priority, pPriority.TextValue AS PriorityText, i.IssueType, pType.TextValue AS TypeText, pType.SortNumber AS TypeOrder,
i.Subtype1, pST1.TextValue AS SubType1Text, pST1.SortNumber AS SubType1Order, i.Subtype2, pST2.TextValue AS SubType2Text,
pST2.SortNumber AS SubType2Order, i.ProjectId, pr.Name AS ProjectName, i.LocationId, lc.Name AS LocationName, i.DepartmentId, dp.Name AS DepartmentName,
i.Escalated, i.AssignedTo, ucAssign.FullName AS AssignedToFullName, i.AssignedAt, i.DueAt, i.ClosedAt, i.ClosedBy, udf.UDFString1, udf.UDFBool1, udf.UDFInt1,
udf.UDFUserId1, udfc1.FullName AS UDFUser1Name, udf.UDFPickList1, udfp1.TextValue AS UDFPickList1Text, udfp1.SortNumber AS UDFPickList1Order, t.TenantId,
uc.LoginEmail, uc.UserId, DATEDIFF(d, i.CreatedAt, GETDATE()) AS Age, i.AttachmentCount
FROM dbo.ITIssue AS i INNER JOIN
dbo.PicklistItem AS p1 ON i.IssueStatus = p1.PicklistItemId LEFT OUTER JOIN
dbo.PicklistItem AS p2 ON i.Substatus = p2.PicklistItemId INNER JOIN
dbo.PicklistItem AS pType ON i.IssueType = pType.PicklistItemId LEFT OUTER JOIN
dbo.PicklistItem AS pST1 ON i.Subtype1 = pST1.PicklistItemId LEFT OUTER JOIN
dbo.PicklistItem AS pST2 ON i.Subtype2 = pST2.PicklistItemId LEFT OUTER JOIN
dbo.PicklistItem AS pSeverity ON i.Severity = pSeverity.PicklistItemId LEFT OUTER JOIN
dbo.PicklistItem AS pPriority ON i.Priority = pPriority.PicklistItemId LEFT OUTER JOIN
dbo.Project AS pr ON i.ProjectId = pr.ProjectId LEFT OUTER JOIN
dbo.Location AS lc ON i.LocationId = lc.LocationId LEFT OUTER JOIN
dbo.Department AS dp ON i.DepartmentId = dp.DepartmentId INNER JOIN
dbo.TenantUser AS uc ON i.CreatedBy = uc.UserId LEFT OUTER JOIN
dbo.TenantUser AS uu ON i.UpdatedBy = uu.UserId LEFT OUTER JOIN
dbo.TenantUser AS uAssign ON i.AssignedTo = uAssign.UserId LEFT OUTER JOIN
dbo.Contact AS ucAssign ON uAssign.ContactId = ucAssign.ContactId LEFT OUTER JOIN
dbo.Contact AS cc ON uc.ContactId = cc.ContactId LEFT OUTER JOIN
dbo.Contact AS cu ON uu.ContactId = cu.ContactId LEFT OUTER JOIN
dbo.UDFValues AS udf ON udf.EntityId = i.IssueId LEFT OUTER JOIN
dbo.TenantUser AS udfu1 ON udfu1.UserId = udf.UDFUserId1 LEFT OUTER JOIN
dbo.Contact AS udfc1 ON udfu1.ContactId = udfc1.ContactId LEFT OUTER JOIN
dbo.PicklistItem AS udfp1 ON udfp1.PicklistItemId = udf.UDFPickList1 INNER JOIN
dbo.Team AS t ON t.TeamId = i.TeamId LEFT OUTER JOIN
dbo.TenantUser AS uRepored ON i.ReportedBy = uRepored.UserId LEFT OUTER JOIN
dbo.Contact AS cReported ON cReported.ContactId = uRepored.ContactId
|
|
|
|
|
Count your blessings. I'd be ecstatic if some of the queries I'm working on currently took only twenty seconds.
One of the techniques I found that improved some of the worst (similar to yours) from several hours to about twenty minutes is the use of Common Table Expressions.
Instead of
SELECT ...
FROM A
INNER JOIN B...
INNER JOIN C...
INNER JOIN D...
You can do
WITH a AS
(
SELECT ...
FROM A
INNER JOIN B ...
)
, b AS
(
SELECT ...
FROM a
INNER JOIN C ...
)
SELECT ...
FROM b
INNER JOIN D ...
|
|
|
|
|
PIEBALDconsult wrote: Count your blessings
I agree. 18-19 seconds on a query that looks like its working with a good number of tables, and joins which is returning 300k rows does seem to horrible. Good luck to him at shaving down anymore time on it.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
I agree with the performance observed. A query which returns 300K rows is typically not a online transaction where a user is waiting for the results to display, so what does it matter if you shave a few seconds off the execution time? (rhetorical question here)
Also noted is that there is no "where clause", so each time this query is run it will take longer and longer. Right? More and more data will be created each day...
If this is an online transaction, then maybe a strategy where summary tables are populated as transactions are being generated might be something to investigate.
|
|
|
|
|
I would strip your query down to the INNER JOINs and execute into a temp table. Optimize this if necessary. Then I would apply the LEFT JOIN lookups to the temp table.
I have found that complex queries can sometimes be sped up by a huge factor when broken up logically. In this case, you have the engine joining and executing numerous correlated subqueries (in the form of LEFT JOINS) at the same time.
|
|
|
|
|
In addition to Michael Potters suggestion, identify the inner join or where clause that reduces the quantity by the most records, this is the same as MPs suggestion except more focused. Shove that into either a temp table or table var. A temp table may have indexes applied if required.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am using full text search to search through PDF documents using the Adobe iFilter. Everything works fine. Now, is it possible that I get a sentence which contains my searched keyword? For example:
Keyword:
'fox'
Query result:
'The quick brown fox jumps over the lazy dog.'
'Fox is a small red animal.'
modified 3-Aug-12 7:53am.
|
|
|
|
|
Member 8024623 wrote: Now, is it possible that I get a sentence which contains my searched keyword?
Ehr.. yes, especially since you already mentioned that it works. Did you give it a try?
I got the feeling that I'm misunderstanding your question. English isn't my native tongue, and it helps if there's a bit explanation and some code to give an indication of what is expected. That said, kudo's for including the example with the expected output 
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
I also think you've misunderstood my question When I said that everything works fine I meant that full text search over PDF documents works. I can retrieve a PDF document that contains a searched keyword. But, I need to retrieve a sentence or sentences from that or any other document that contain that keyword.
|
|
|
|
|
asimptota777 wrote: But, I need to retrieve a sentence or sentences from that or any other document that contain that keyword.
Then you will need IFilters for each type of document that's in your database. There's no way of reading "every" document, since each fileformat (and their versions) have different encodings and layouts.
You can download those for Office here[^] (2007/2010).
If you have a document in there in your own file-format (binary serialized data?) you'd probably have to provide your own IFilter implementation (guidelines on MSDN).
All existing and wide-used formats should have an implementation, since the same IFilter is in use for Desktop Search, SharePoint and the likes. And of course, CodeProject[^] has a lot of articles on the subject.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thank you for an answer but I think you haven't read my question carefully. In my first post I said that I store PDF documents in the database (in the Filestream to be precise)and I use Adobe iFilter for full text search through PDF documents. That works fine. What I need is a way to get sentences from those PDF documents that contain a certain keyword. What T-SQL syntax can I use to extract sentences that contain a certain word?
|
|
|
|
|
asimptota777 wrote: What T-SQL syntax can I use to extract sentences that contain a certain word?
USE AdventureWorks2012;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'performance'
SELECT Description
FROM Production.ProductDescription
WHERE FREETEXT(Description, @SearchWord);
From MSDN[^]
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Ok, I know about FREETEXT. But with this command you get THE WHOLE Description field. I need to get only the sentence that contains a keyword.
Query:
SELECT Description
FROM Production.ProductDescription
WHERE FREETEXT(Description, 'smooth');
Result:
1. Suitable for any type of riding, on or off-road. Fits any budget. Smooth-shifting with a comfortable ride.
2. Top-of-the-line competition mountain bike. Performance-enhancing options include the innovative HL Frame, super-smooth front suspension, and traction for all terrain.
3. Aerodynamic rims for smooth riding.
4. Excellent aerodynamic rims guarantee a smooth ride.
I need result like this:
1. Smooth-shifting with a comfortable ride.
2. Performance-enhancing options include the innovative HL Frame, super-smooth front suspension, and traction for all terrain.
3. Aerodynamic rims for smooth riding.
4. Excellent aerodynamic rims guarantee a smooth ride.
|
|
|
|
|
asimptota777 wrote: Ok, I know about FREETEXT. But with this command you get THE WHOLE Description field. I need to get only the sentence that contains a keyword.
..it doesn't work that way; the document is returned, as it could contain the searched word more than once, in multiple locations. You can easily write some code to find the sentence with the word that was searched and extract it.
asimptota777 wrote: Result:
That's a list of requirements, not a programming question.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Dude, I know what full text search is and how it functions. I just don't know how to implement the functionality I've mentioned. I must admit that your signature really suits you...
|
|
|
|
|
asimptota777 wrote: I must admit that your signature really suits you...
Thank you - it was hard to earn that title 
You could repost the question with a link to this thread. State in the new post that this wasn't helpful, and someone might come up with something better.
asimptota777 wrote: <layer>Dude, I know what full text search is and how it functions. I just don't know how to implement the functionality I've mentioned.
I did not look at the requirements; technically, you want to retrieve documents based on a searchterm. You get a list of documents that contain that term. You can retrieve the document.
What's keeping you from doing a substring on that document and parse out the line? You can fetch the index where the word is in the document, then you work your way back to the first word with a capital letter, and forward to the first interpunction.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Hi all,
Every time my code tries to connect to my 2007 Access database, the message Unrecognized Database Format pops up. I've read that it is usually due to a corrupt database file or opening an old Access database file in newer version of Access Database. I can rule out opening an old file in newer Access because I've created and run the file in Access 2007. I can reasonably assume that since I can open the file, it is not corrupt. If those two aren't the causes what else could be causing it? The connection string in my web.config file takes the following form:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;
I don't know if that has anything to do with it, any suggestion will be greatly appreciated, thanks for your time.
|
|
|
|
|
This information is REALLY old, there used to be an option to compile and compress an Access database. If this option is still available I suggest you use that. Being able to open the database does not mean it is not corrupted.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
ASPnoob wrote: I can reasonably assume that since I can open the file, it is not corrupt.
Wrong assumption. The database is corrupt; usually when Access terminates within a write - it can not ammend the database with random bites when it comes back online, so, you get the next best thing; you get to see the database, as Access thinks that the underlaying data should be.
So, install Sql Server Express and start upsizing all the tables that are still within the database, and have the Wizard migrate the data with it.
Did your database allow access to multiple users simultaneous?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thank you, everyone for responding. My database was created less than 10 minutes prior to testing it in my code. It only has one table with one field in the table. I just cannot understand how it could be corrupted. I will give everyone's suggestion a try, thanks again for your reply.
|
|
|
|
|
Did you use Microsoft Access in an environment where multiple users would manipulate data at the same time? Is your database on some network-share?
Microsoft Access is a damn good tool to manipulate single-user local-file databases with strong reporting capabilities.
It's not built to be a sharing-facility for data. If that's what you need, you'll need to switch to a real server - otherwise crap like this will happen again.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
If we are selecting a set of data, and one of the fields is erroring out, with an arithematic over flow error, how to move this row aside and continue processing the remaining rows so that the entire stored procedure does not fail?
More like move this row causing the issue into a table and continue processing remaining rows?
Is there like on error resume next?
I tried a try catch block.
With in my try I had a select statement with a good value and one with a bad value, I wanted the select to print the good and not the bad one.
======================
declare @dummy int
begin try
select
--right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
select
--right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
end try
begin catch
set @dummy = 1
end catch
print @dummy
modified 2-Aug-12 11:06am.
|
|
|
|
|
 try a bigint, in place of the int.. Care to explain why you are rounding the 50000, and what the casting is all about?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
To give a bit more history, we are creating this view to feed into another application so it has to be set length.
Suppose this view returns 100 rows, and I am having an arthimetic overflow in on row 56 only. I want to leave 56 and continue from 57. WhenI tried the try catch block, while it catches the error, but on once it hits 56, then the whole thing stops.
|
|
|
|
|
vanikanc wrote: To give a bit more history
Did you try what I suggested? Y/N?
vanikanc wrote: I want to leave 56 and continue from 57.
Filter the row out before casting it into infinity, make the result-variable bigger, or try padding it with zeroes after the cast.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
