current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 10 down vote favorite

I created a linked server in SSMS so that I could run reports on a SpiceWorks SQLite database. there are a few quirks in the whole system.

These simple queries are becoming some of the most expensive queries on my reporting SQL Server, I kind of know why, is there a way that I can do this differently?

The Code

I have two queries.

  1. TimeToCloseByAssignee 

    SELECT 
    id AS ticketNum
    , Summary
    , Assignee
    , closed_at
    , created_at
    , ROUND((DATEDIFF(Hour, created_at, closed_at))/24. , 2) AS DaysOpen
FROM OPENQUERY(SPICEWORKS,'
    SELECT tickets.id 
        , users.email as Assignee
        , substr(tickets . summary, 1,100) AS Summary
        , tickets.closed_at
        , tickets.created_at
    FROM tickets
        INNER JOIN users ON tickets.assigned_to = users.id
WHERE status=''closed''
        AND 
        master_ticket_id IS NULL;')

  2. GetCreatorByTicketID

    SELECT * FROM OPENQUERY(SPICEWORKS, '
    SELECT users.email
        , tickets.id
    FROM tickets
        INNER JOIN users ON tickets.created_by = users.id')
WHERE id = @TicketID

I am using SSRS to handle the nice presentation of the data.

the problem with this, is that I can't give the underlying SQLite query boundaries from SSRS Parameters, it just doesn't like it. There are some questions on StackOverflow with answers and ways to do it, but this was the only way that I could make it work the way I wanted it to. perhaps I wasn't interpreting the answers correctly. either way I don't think it would have been clean and straightforward anyway.

Any thoughts?

share|improve this question
    
Just a minor suggestion, but maybe you could specify the columns to retrieve from the second query. –  ArthurChamz Mar 19 '14 at 17:14
    
@ArthurChamz What? –  Malachi Mar 19 '14 at 17:17
1  
I mean, instead of using SELECT *, write the column names (stackoverflow.com/questions/1867946/…). I'm well aware it's probably the least of the problems, though. –  ArthurChamz Mar 19 '14 at 17:28
    
why would I type more code that could contain bugs than I have to? @ArthurChamz ? I don't need to write out all the column names, this is a pass-through query. I know what I am getting, and it is going straight into a report from here. I don't need more places where my dumb fingers can mistype something and cause issues on this. thanks I like the idea but it isn't a good one (if that makes sense). –  Malachi Mar 19 '14 at 17:31
    
@ArthurChamz, I would agree with you if I was only writing the query in one spot, but I need all the fields that I select from the SQLite database and that won't change in this case –  Malachi Mar 20 '14 at 15:30

2 Answers 2

up vote 6 down vote

Ouch.... you are not filtering the data you are selecting from the OPENQUERY source....

But, let's get some things straight first.... you say:

either way I don't think it would have been clean and straightforward anyway

Using OPENQUERY automatically excludes any pretext of 'clean and straightforward'. They are incompatible.

So, as soon as you use OPENQUERY you can assume that you have already got ugly code (with a fat ass).

The trick is to use makeup - to cover up the blemishes.

In this case, you can filter the records, but you need to be creative.... you have:

SELECT * FROM OPENQUERY(SPICEWORKS, '
    SELECT users.email
        , tickets.id
    FROM tickets
        INNER JOIN users ON tickets.created_by = users.id')
WHERE id = @TicketID

I would suggest the following:

declare @ticketsql as nvarchar(1024)
;

set @ticketsql = '
    SELECT users.email
        , tickets.id
    FROM tickets
        INNER JOIN users ON tickets.created_by = users.id
    WHERE tickets.id = ' + @TicketID
;

select * FROM OPENQUERY(SPICEWORKS, @ticketsql)
share|improve this answer
    
I tried this and couldn't get it to work, but I use a VARCHAR(8000) and not a NVARCHAR(1024) –  Malachi Mar 19 '14 at 16:43
    
I am going to try nesting queries to get rid of the second query altogether. –  Malachi Mar 19 '14 at 16:47
    
it doesn't like that @rolfl, says Incorrect syntax near '@TicketID, Expecting STRING, or TEXT_LEX.` –  Malachi Mar 19 '14 at 17:22
    
Convert(NVarchar(50), @TicketID) instead when doing the concatenation –  rolfl Mar 19 '14 at 17:25
    
OPENQUERY doesn't like being passed a Variable there @rolfl if I just put a string in there without the reference to a parameter it still doesn't like it. –  Malachi Mar 19 '14 at 17:35
up vote 6 down vote accepted

I was able to remove the second query and merge it with the first one like this, I don't know why I didn't try it like this to begin with, probably because I wanted to tread lightly on this shaky bridge. I still don't have a solid way of passing variables to the OPENQUERY, but that is a question perhaps for StackOverflow when I have more time to work on this project.

Code:

SELECT 
    id AS ticketNum
    , Summary
    , Assignee
    , closed_at
    , created_at
    , Round((DATEDIFF(HOUR,created_at, closed_at))/24. , 2) AS DaysOpen
    , Creator
FROM OPENQUERY(SPICEWORKS, '
    SELECT tickets.id
    , users.email AS Assignee
    , substr(tickets . summary, 1,100) AS Summary
    , tickets.closed_at
    , tickets.created_at
    , table2.email AS Creator
FROM
    tickets
    INNER JOIN users ON tickets.assigned_to = users.id
    INNER JOIN users AS table2 ON tickets.created_by = table2.id 
WHERE tickets.status = ''closed'' AND tickets.master_ticket_id IS NULL;')

what I did here was turn the other query into a separate join to the users table but joined the aliased table on another field in the tickets table. this way I can get both the creator and the assigned user (Assignee) from this single query.

getting rid of the second Query reduced the amount of queries to the SQLite database to just one, the other way was executing the second query an outrageous amount of times because the second query would run once for every row in the first query, THIS IS HORRIBLY INEFFICIENT, do not do this unless you absolutely have to.

I should have explained more, the second query had to be a sub-report of the first one in order for it to work the way I wanted it to. I didn't think I could alias tables in a query run this way, I wasn't fully qualifying the names of all my columns once I put an alias in, and that is why I didn't do that the first go round, but now I know that I can do this on SQLite as long as I fully qualify all the columns I select in the query run on the SQLite database

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.