How can I simplify this query to not kill SQL server-CodePudding

So I was tasked to look up several tickets in the database that hit certain criteria. But I think I am at the limitations of what SQL can handle the below has an issue because all tickets that are belonging to company '1646' are like over 12k tickets for that customer alone...

So at first I thought about an inner join statement but then I started to get a headache as things kept looping around itself. Below is the script that attempts to run but then my database starts to smoke.. and suffers read/write issues and the lag becomes very real and it is very clear to see it is from me pulling this query, The culprit:

     SELECT 
        s.SR_Service_RecID as 'Ticket #',
        t.Description as 'Description',
        s.Summary as 'Summary Description',
        d.Date_Created as 'Entered',
        b.Board_Name as 'Board',
        c.Company_ID as 'Company',
        q.Description as 'Status',
        p.SR_Severity_Name as 'Priority',
        a.Description as 'Type',
        z.Description as 'SubType',
        s.Date_Closed as 'Closed'
    d.SR_Detail_Notes_Markdown as 'Notes',
    From 
        dbo.SR_Service as s,
        dbo.SR_Type as t,
        dbo.SR_Detail as d,
        dbo.SR_Board as b,
        dbo.Company as c,
        dbo.SR_Status as q,
        dbo.SR_Severity as p,
        dbo.SR_Type as a,
        dbo.SR_Subtype as z
    WHERE 
    s.Company_RecID like '1646'
        and s.Entered_By not like 'zadmin'
        and s.Updated_By not like 'zadmin'
        and s.Entered_By not like 'RESTAPI'
        and s.Updated_By not like 'RESTAPI'
        and s.Entered_By not like 'techautomate'
        and s.Updated_By not like 'techautomate'
        and s.Entered_By not like 'template%'
        and s.Updated_By not like 'template%'
        and s.Entered_By not like 'HelpDesk'
        and s.Updated_By not like 'HelpDesk'
        and s.Entered_By not like 'Email Connector'
        and s.Updated_By not like 'Email Connector'
        and d.SR_Detail_Notes_Markdown not like '%Assigned%'
    ORDER BY 
        s.Date_Entered ASC;`

So if anyone has the time or if someone can help me refine this into a better query or help me figure out the figure 8 of inner joins to make this work, or perhaps there is a SQL chad out here that can easily write this into a SQL script I would be most grateful for the help in figuring this out. Whatever the outcome thank you in advance for the help!!

--EDIT-- Allow me to add a little more detail...Sorry for the confusion guys, I do appreciate the help

So I am going to add the Friendly names first then the primary key and foreign key relations

ticket number = Service_RecID is the primary key in dbo.Service foriegn key in dbo.Detail

ticket type = SR_Type_RecID is the primary key in dbo.Type foreign key in dbo.Service -> but I need the description of that id which is in dbo.type

Summary Description = is located in dbo.SR_Service the column header is Summary

Entered (is the date the ticket was entered) = located in dbo.SR_Detail under the column header Date_created

Board (is the service board ticket is assigned to) = SR_Board_RecID primary key in dbo.SR_Board foreign key in SR_Service -> but I need the column header Board_Name located in dbo.SR_Board

Company = Company_RecID primary key in dbo.Config but the foreign key in dbo.SR_Service

Status = SR_Status_RecID primary key dbo.SR_Status foriegn key is located in dbo.SR_Service

Priority = SR_Severity_RecID primary key for dbo.SR_Severity foriegn key is located in dbo.SR_Service -> but I need SR_Severity_Name which is the column header in dbo.SR_Severity

type = SR_Type_RecID primary key in dbo.Type shares foreign key with dbo.Service -> but I need the description associated with SR_Type_RecID located in dbo.SR_Type

subtype = SR_SubType_RecID primary key located in dbo.SR_subtype and the foreign key that is shared is under dbo.SR_service -> but again I need the description that is under the dbo.SR_SubType

closed = as it is located in the dbo.SR_Service under column header date_closed

notes = on the other hand is located in dbo.Detail under column header SR_Detail_Notes_Markdown and the only keys it shares across the database is a foreign key SR_Service_Rec_ID

I hope that this helps to clarify the parabola of terror I am playing in :)

---EDIT 2---

I cant seem to get the INNER JOIN statements to work properly

    INNER JOIN dbo.SR_Service.Service_RecID on dbo.SR_Detail.Service_RecID
    INNER JOIN dbo.Type.SR_Type_RecID on dbo.SR_Service.SR_Type_RecID
    INNER JOIN dbo.Type.Description on dbo.SR_Service.Type_Description
    INNER JOIN dbo.SR_Board.SR_Board_RecID on dbo.Service.SR_BoardRecID
    INNER JOIN dbo.Config.Company_RecID on dbo.Service.Company_RecID
    INNER JOIN dbo.SR_Status.SR_Status_RecID on dbo.Service.SR_Status_RecID
    INNER JOIN dbo.SR_SubType.SR_Type_RecID on dbo.Type.SR_Type_RecID

I think it is how I am declaring my FROM Statement...

Can I not just run singular database queries and have the results dump and append to a new database object?

CodePudding user response：

As Martin Smith says, you need to define the table joins. As the query stands now, any results would be useless.

To do this, you must first understand the database schema. More specificially the relationship between the entities (= tables). For this you need to know and understand the business domain and how it is represented in the tables. The sql server experts from this forum will not be able to do this for you without the knowledge of the business domain.

After you have achieved the above you can deduce the correct joins from the database schema and improve your query draft accordingly.

CodePudding user response：

The JOIN condition is how one table is related to another based on a common column. Aside from your specific query, think of orders.

An order is made by a customer. An order has details, order detail products are from a products table.

Having said that, you might want something like

select
        c.customername,
        o.orderdate,
        od.qty,
        p.productname
    from
        customer c
            join orders o
                on c.customerid = o.customerid
                join orderDetails od
                    on o.orderid = od.orderid
                    join products p
                        on od.productid = p.productid

FEEDBACK FROM EDITS you provided in your original question

Now that you have provided respective primary key and foreign keys, we can help a little further. First, dont try to name your columns with spaces or special characters like 'Ticket #'. It is typically easier to name them readable in 'CamelCaseWhereUpperPerWord' is just an example. The output via whatever method should worry about the formatting of such header columns in output.

Next, when applying filters, such as your company number, if its a number, dont put it in quotes, leave it as a number for equality testing. In this case, you are looking for tickets for a single company = 1646. I would ensure an index on the service table including that column as an index in its first position.

From your edited PK/FK, I have revised the query as below with embedded comments

SELECT 
        -- per ticket found
        s.SR_Service_RecID TicketNumber,
        -- dont worry about renaming Summary AS SummaryDescription,
        -- let the OUTPUT process add whatever column headers is more common approach
        -- just get the column and get it to work first, then fine-tune it
        s.Summary,
        s.Date_Closed Closed,

        -- dont worry for now about specific column order, I am trying to match
        -- the hierarchy of data trying to acquire.  You want for a specific company,
        -- so I am just putting that up front for now.
        c.Company_ID Company,

        -- description from the TYPE table
        t.Description type,

        -- pull from sub-type of the type
        st.Description SubType,

        -- now I can pull columns from the SR_Detail table
        d.Date_Created Entered,
        d.SR_Detail_Notes_Markdown Notes,

        -- now any columns from the SR_Board table
        b.Board_Name Board,

        -- columns from the status table
        q.Description Status,

        p.SR_Severity_Name Priority
    From 
        -- or is this table SUPPOSED to be Service vs SR_Service
        SR_Service as s
            -- first, joining to the config table so you can get the descriptive company "name", such as your "Company_ID"
            JOIN Config as c
                on s.Company_RecID = c.Company_RecID

            -- now join the outer hierarchy SR_Service to the SR_Type on its PK/FK relationship
            join SR_Type as t
                on s.SR_Type_RecID = t.SR_Type_RecID

            -- changing the subtype to a LEFT-JOIN in case a sub-type does not exist
            LEFT JOIN SR_Subtype as st
                on s.SR_SubType_RecID = st.SR_SubType_RecID

            -- now join the outer hierarchy SR_Service to the SR_Detail on its PK/FK relationship
            -- or is this table SUPPOSED to be Detail vs SR_Detail
            JOIN SR_Detail as d
                on s.Service_RecID = d.Service_RecID

            -- doing a LEFT-JOIN since an entry may NOT be assigned to a board (yet).
            -- otherwise the ticket will be excluded from final list if no such board assignment
            LEFT JOIN SR_Board as b
                on s.SR_Board_RecID = b.SR_Board_RecID

            -- etc., similar joins to each other lookup table for clear descriptions based on given PK/FK relationship
            JOIN SR_Status as q
                on s.SR_Status_RecID = q.SR_Status_RecID

            JOIN SR_Severity as p
                on s.SR_Severity_RecID = p.SR_Severity_RecID

    WHERE 
        -- NOW, you can apply your filtering conditions here as you have them
            s.Company_RecID = 1646
        and s.Entered_By not like 'zadmin'
        and s.Updated_By not like 'zadmin'
        and s.Entered_By not like 'RESTAPI'
        and s.Updated_By not like 'RESTAPI'
        and s.Entered_By not like 'techautomate'
        and s.Updated_By not like 'techautomate'
        and s.Entered_By not like 'template%'
        and s.Updated_By not like 'template%'
        and s.Entered_By not like 'HelpDesk'
        and s.Updated_By not like 'HelpDesk'
        and s.Entered_By not like 'Email Connector'
        and s.Updated_By not like 'Email Connector'
        and d.SR_Detail_Notes_Markdown not like '%Assigned%'
    ORDER BY 
        s.Date_Entered ASC;

Now that you have a full query, in the future, take one piece at a time. Look at what you wanted. All tickets associated with one customer. Query just that. As you have a table (or alias), get all the columns you expect from that one before moving on. Ex:

select
        s.SR_Service_RecID TicketNumber,
        s.Summary,
        s.Date_Closed Closed
    from
        SR_Service s
    where
        s.Company_RecID = 1646

Great, this gets you the immediate result of the tickets. But now you want more stuff. So, one table at a time, do a join. How does the second table relate to the first. I always try to list my primary (in this case your service table) on the left (or first position) JOINING a second table (right-side) based on what FK/PK relationship. In this case, you want the in-the-clear company name from the Config table. So, get that extra column based on the join itself.

select
        s.SR_Service_RecID TicketNumber,
        s.Summary,
        s.Date_Closed Closed,
        -- and now columns from next table
        c.Company_ID Company
    from
        SR_Service s
            JOIN Config as c
                on s.Company_RecID = c.Company_RecID
    where
        s.Company_RecID = 1646

Continue adding one table at a time and one WHERE clause at a time until the entire query is done. In SQL-Server, by doing a

SELECT TOP 10 (rest of query)

will result the list to only 10 records. This way, you can sample "Does this query work as written?" Do you get an answer? Have you botched the command already? Fix it early one piece at a time. Column between each column retrieved. Do I have the proper JOIN condition between the two tables? then move on.

Hopefully this detailed example of your database scenario will help you learn to write queries easier. Also see how to better list your table structures (ALL RELEVANT COLUMNS) to help get better responses vs us trying to guess at column names in tables.