temp tables

[nc297]

I have two questions...

1. I have this long query where I want to get certain information out of and placed into a temp table. Is this the most efficient way or should I use a CTE?

SELECT n.DOC, ISNULL(pendinfo1, 0) AS PendInFO1, ISNULL(pendinfo2,0) AS pendinfo2, ISNULL(pendinfo3,0) AS pendinfo3,
ISNULL(pendFOover1,0) AS pendFOover1,ISNULL(pendFOover2,0) AS pendFOover2,ISNULL(pendFOover3,0) AS pendFOover3

into #temppd
FROM Natdocfile AS n

Left Join
---Pending in FO1
(Select t.doc, ISNULL(COUNT(t.clmn ), 0)as pendinfo1
from t2dibpend t
where DIB_MVT_SEQ_NUM IS NULL and t.clmn in (select clmn from specnew.dbo.people where completedt is null)
group by t.doc)a
on n.doc = a.doc
Left Join

--Pending in FO2
(select t.doc, ISNULL(COUNT(t.clmn ), 0) as PendInFO2, max(dib_mvt_seq_num) as MaxFO2
From t2DibPend AS t
where(DIB_MVT_TYP='r') and not (org_id like 'R%' or org_id like 'S%' or org_id like 'V%' or
org_id like 'P%') and t.clmn in (select clmn from specnew.dbo.people where completedt is null)
group by t.doc)b
on n.doc=b.doc
LEFT JOIN

--Pending in FO3
(select t.doc, ISNULL(COUNT(t.clmn ), 0) as PendInFO3, max(dib_mvt_seq_num)as MaxFO3
From t2DibPend t
where (DIB_MVT_TYP='t')
group by t.doc)c
on n.doc = c.doc
LEFT JOIN

--Pending in FO1 > 300
(Select t.doc, ISNULL(COUNT(t.clmn ), 0) as PendFOover1
from t2dibpend t
where DIB_MVT_SEQ_NUM IS NULL and app_rcpdt< dateadd(dd,-300,getdate()) and t.clmn in (select clmn from specnew.dbo.people where completedt is null)
group by t.doc)d
on n.doc = d.doc
LEFT JOIN

--Pending in FO2 > 300
(select t.doc, ISNULL(COUNT(t.clmn ), 0) as PendFOover2, max(dib_mvt_seq_num) as MaxoverFO2
From t2DibPend AS t
where(DIB_MVT_TYP='r') and not (org_id like 'R%' or org_id like 'S%' or org_id like 'V%' or
org_id like 'P%') and app_rcpdt< dateadd(dd,-300,getdate()) and t.clmn in (select clmn from specnew.dbo.people where completedt is null)
group by t.doc)f
on n.doc=f.doc
LEFT JOIN
--Pending in FO3 > 300
(select t.doc, ISNULL(COUNT(t.clmn ), 0) as PendFOover3, max(dib_mvt_seq_num)as MaxoverFO3
From t2DibPend t
where (DIB_MVT_TYP='t')and app_rcpdt< dateadd(dd,-300,getdate()) and t.clmn in (select clmn from specnew.dbo.people where completedt is null)
group by t.doc)g
on n.doc=g.doc


2. I didn't know how to combine all three of the queries together so I did them separately and in the other table I'm adding them up to get what I want. So in the next query I'm doing this to get what I want:

(SELECT t.doc, SUM(t.pendinfo1 + t.pendinfo2 + t.pendinfo3) AS pendinfo
FROM (SELECT *
FROM #temppd) t
GROUP BY t.doc)d
ON n.doc = d.doc

LEFT JOIN
(SELECT t.doc, sum(t. PendFOover1 + t.PendFOover2 + t.PendFOover3) as pendfo300
FROM (SELECT *
FROM #temppd) t
GROUP BY t.doc)f
ON n.doc = f.doc

Is there an easier way to combine all? It does give me what I want.

 

[gmmastros]

Indexes are a huge topic. But, at a basic level, you need to understand this:

1. Indexes can speed up queries.

Indexes allow SQL Server to quickly find data in your table. Think of your table as a dictionary. If I asked you to find all the words that start with 'be', you would quickly flip through the pages and find the first and last words. Why? because you know that in a dictionary, all of the entries are alphabetized. If I handed you a regular book and told you to do the same thing, it would take much longer.

2. Indexes can slow down queries.

Indexes are maintained as separate objects in the database. When you add, update, or delete rows, the index data must be modified to accommodate it. Again, think about the dictionary. If you add a new word, you would need to make space on the correct page and then push down all the remaining data to accommodate the new word. SQL Server does something similar. When you insert data, it first checks to see if the data fits within the page. If it doesn't, it will create another page and move 1/2 the data from the original page to the new one, and then it will insert the new data. You're left with 2 data pages that are 1/2 full. By the way, this is how indexes get fragmented.

Do I always place it in a table and on the PK?

It all depends on how you want to think about it. Behind the scenes, a clustered index IS the table. non-clustered indexes are separate objects. Of course, even a non-clustered index cannot exist without the table.

Can I have two indexes in the same table?
Yes. In fact, most tables (in my database) have multiple indexes.

Is there max number of indexs for a table?
Yes. But it's a really big limit.

In practice, every table should have a primary key. Sometimes the primary key is a single column, and sometimes it is multiple columns. Regardless, there should be a primary key. When you add a primary key to a table, SQL Server will (by default) create a clustered index on the table. This is (almost) always the right thing to do. However, there are plenty of times where additional indexes will improve query performance.

In my database, I have some relatively large tables that rarely change. For these tables, I have a bunch of indexes (about 8 or 9). You see, for tables that you run a lot of queries on, but the data doesn't change too often, it benefits you to put a lot of indexes on the data. For tables that are big and change frequently, it's better to have a small number of indexes (2 or 3).

Indexing a database is a balancing act. You need to weigh the performance of all aspects (select, insert, update, and delete).

If you are using SQL Express, there is no query tuning wizard, but for all other versions of SQL Server, there is a tuning wizard that can analyze your query and the table(s) involved. It will sometimes make suggestions regarding indexes (and statistics) that would improve query performance. In SQL Server Management Studio, click QUERY -> Analyze Query in Database Engine Tuning Advisor.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nc297]

Thanks again! I'm printing all and reviewing! You explained it nicely for a beginner!