Query Performance

I have two tables.
Employee
EmployeeCode int Primary Key

Employee_Stock
EmployeeCode int
StockCode varchar(10)
Primay key on (Employeecode, StockCode)..

There is no foreign key relation between these 2 tables.
Now my question is which query give more performance. and why?
1. Select * from
Employee INNER JOIN Employee_Stock on Employee.Employeecode = Employee_Stock.EmployeeCode

2. Create a foreign Key between Employee and Employee_Stock for EmployeeCode. and run the same query.

Actually we forgot to put the foreign key relationship between these 2 tables and we have lot of queries joining them..
Now if we add foreignkey, is it going to improve the performance or not?

Thanks
RameshForeign keys enforce business rules and relational integrity, but in themselves do not increase performance. In some cases they may actually decrease performance while the database maintains relational integrity during inserts, updates, and deletes. You should still establish them to save you all sorts of headaches later when you find that your data does not make sense.

If you have already have indexes on your joined columns then I don't think your query is about as efficient as it is going to get. Show a query plan and verify that the indexes are being used.

Query Parameter Problem

I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT * FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When I preview the report, no problem is found. But when I deploy it in report server, the following error is shown:
ORA-01036: illegal variable name/number
In fact, both report server and report designer are installed in same machine.Please try these steps:
* delete the published report from the report server (through the report
manager: http://localhost/reports)
* verify that your report really works in Report Designer Preview (click the
green "refresh" icon in the Preview toolbar)
* deploy the report again to report server
* try to run it again through report manager
BTW: is there any specific reason why you choose the MS OleDB provider for
ODBC and connect to an Oracle ODBC data source?
Why don't you use the managed Oracle provider (by selecting "Oracle" in the
data source dialog) which would support named parameters (example for
parameter syntax: SELECT * FROM EMPLOYEE WHERE EMPL_ID = :employeeID )? You
can also use the MS OleDB provider for Oracle (which directly connects to
Oracle, rather than using the ODBC provider) or the Oracle OleDB provider.
Note: when designing Oracle queries you should always use the text-based
query designer (with 2 panes) rather than the graphical query designer (with
4 panes).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <May Liu@.discussions.microsoft.com> wrote in message
news:4FEB9C4F-DF19-451C-899D-37A63F53389D@.microsoft.com...
> I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT *
FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When
I preview the report, no problem is found. But when I deploy it in report
server, the following error is shown:
> ORA-01036: illegal variable name/number
> In fact, both report server and report designer are installed in same
machine.|||thanks !!!
The error is gone if I use MS OleDB provider for Oracle.
"Robert Bruckner [MSFT]" wrote:
> Please try these steps:
> * delete the published report from the report server (through the report
> manager: http://localhost/reports)
> * verify that your report really works in Report Designer Preview (click the
> green "refresh" icon in the Preview toolbar)
> * deploy the report again to report server
> * try to run it again through report manager
> BTW: is there any specific reason why you choose the MS OleDB provider for
> ODBC and connect to an Oracle ODBC data source?
> Why don't you use the managed Oracle provider (by selecting "Oracle" in the
> data source dialog) which would support named parameters (example for
> parameter syntax: SELECT * FROM EMPLOYEE WHERE EMPL_ID = :employeeID )? You
> can also use the MS OleDB provider for Oracle (which directly connects to
> Oracle, rather than using the ODBC provider) or the Oracle OleDB provider.
> Note: when designing Oracle queries you should always use the text-based
> query designer (with 2 panes) rather than the graphical query designer (with
> 4 panes).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "May Liu" <May Liu@.discussions.microsoft.com> wrote in message
> news:4FEB9C4F-DF19-451C-899D-37A63F53389D@.microsoft.com...
> > I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT *
> FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When
> I preview the report, no problem is found. But when I deploy it in report
> server, the following error is shown:
> > ORA-01036: illegal variable name/number
> >
> > In fact, both report server and report designer are installed in same
> machine.
>
>|||My SQL is base on two parameters. One is mandatroy and the other is optional.
e.g.
SELECT DEPART_NO, EMPL_ID FROM EMPL_TABLE
WHERE DEPART_NO = :DEPT_NO AND EMPL_ID = :EMPL_ID
:DEPT_NO is mandatory
:EMPL_ID is optional
When user input null/blank employee ID, no result is retrieved. How can I override the SQL so that if user don't put employee ID, the SQL become
SELECT DEPART_NO, EMPL_ID FROM EMPL_TABLE
WHERE DEPART_NO = :DEPT_NO
I am using MS OleDB provider for Oracle.
"May Liu" wrote:
> thanks !!!
> The error is gone if I use MS OleDB provider for Oracle.
> "Robert Bruckner [MSFT]" wrote:
> > Please try these steps:
> > * delete the published report from the report server (through the report
> > manager: http://localhost/reports)
> > * verify that your report really works in Report Designer Preview (click the
> > green "refresh" icon in the Preview toolbar)
> > * deploy the report again to report server
> > * try to run it again through report manager
> >
> > BTW: is there any specific reason why you choose the MS OleDB provider for
> > ODBC and connect to an Oracle ODBC data source?
> > Why don't you use the managed Oracle provider (by selecting "Oracle" in the
> > data source dialog) which would support named parameters (example for
> > parameter syntax: SELECT * FROM EMPLOYEE WHERE EMPL_ID = :employeeID )? You
> > can also use the MS OleDB provider for Oracle (which directly connects to
> > Oracle, rather than using the ODBC provider) or the Oracle OleDB provider.
> >
> > Note: when designing Oracle queries you should always use the text-based
> > query designer (with 2 panes) rather than the graphical query designer (with
> > 4 panes).
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> >
> > "May Liu" <May Liu@.discussions.microsoft.com> wrote in message
> > news:4FEB9C4F-DF19-451C-899D-37A63F53389D@.microsoft.com...
> > > I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT *
> > FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When
> > I preview the report, no problem is found. But when I deploy it in report
> > server, the following error is shown:
> > > ORA-01036: illegal variable name/number
> > >
> > > In fact, both report server and report designer are installed in same
> > machine.
> >
> >
> >

Query Optimization - Suggestion needed

Hi,

I need an advice from some SQL Guru. I have a Cursor (SQL Server 2005) which calculates the monthly employee wages. The outer query selects all the employees and the inner query computes the monthly salary day by day in a loop. For each day in a month, i insert a record in a temporary table. So for 2 employees, there can be 2 x 31 records (31 are the maximum days in a month). Later i rotate the temporary table to convert the 31 rows for each employee into one record so at the end there are only 2 employee records but with 31 columns. The problem is that the rotation is taking a huge performance hit and almost takes 5 minutes to convert rows to columns. I have even tried the PIVOT statement but that too almost takes the same time.

Could anyone suggest me how can i nail down a performance efficient query for a monthly report. As far as i know, even on Mainframes, monthly/annual reports takes a long time to compile. So if any GURU has a better idea, i would really appreciate it.

I am only asking about how would any Guru/Expert handle monthly/annual reports with huge amount of data in the tables efficiently.

Regards.......

I suggest that you also post on http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1, the MSDN forum on TSQL.|||

Thanks for the link, i think its a better place to post my question.

Regards......

|||

Hi,

Cursor has a very poor performance and you should avoid it.

Visit http://www.sql-server-performance.com/default.asp. In lower portion you will get many tips to improve your query performance.

I will suggest you to post this question in sql-server-performance.com forum.

Query Optimization - Please Help

Hi,

Can anyone help me optimize the SELECT statement in the 3rd step? I am actually writing a monthly report. So for each employee (500 employees) in a row, his attendance totals for all days in a month are displayed. The problem is that in the 3rd step, there are actually 31 SELECT statements which are assigned to 31 variables. After I assign these variable, I insert them in a Table (4th step) and display it. The troublesome part is the 3rd step. As there are 500 employees, then 500x31 times the variables are assigned and inserted in the table. This is taking more than 4 minutes which I know is not required :). Can anyone help me optimize the SELECT statements I have in the 3rd step or give a better suggestion.

DECLARE @.EmpID, @.DateFrom, @.Total1 ... // Declaring different variables

SELECT @.DateFrom = // Set to start of any month e.g. 2007-06-01 ..... 1st

Loop (condition -- Get all employees, working fine)

BEGIN

SELECT @.EmpID = // Get EmployeeID ..... 2nd

SELECT @.Total1 = SUM (Abences) ..... 3rd

FROM Attendance

WHERE employee_id_fk = @.EmpID (from 2nd step)

AND Date_Absent = DATEADD ("day", 0, Convert (varchar, @.DateFrom)) (from 1st step)

SELECT @.Total2 ..................... same as above

SELECT @.Total3 ..................... same as above

INSERT IN @.TABLE (@.EmpID, @.Total1, ..... @.Total31) ..... 4th

Iterate (condition) to next employee ..... 5th

END

It's only the loop which consumes the 4 minutes. If I can somehow optimize this part, I will be most satisfied. Thanks for anyone helping me...

What does the Attendance table look like? I have some ideas for you but I need to know how the attendance is stored. Can you give us the schema of that table please? Thanks!

|||

See this sample example-->

========================================================================================

Declare @.fromDate datetime,
@.toDate datetime

Set @.fromDate = '1-Aug-2007'
Set @.toDate = '4-Aug-2007'

Select distinct a.dtAttendate,

'STATUS'=(select
(case
when lv_status='EL' then 'EL'
when lv_status='CL' then 'CL'
when lv_status='SL' then 'SL'
when lv_status='ML' then 'ML'
when (wk_status='S' AND log_status='P') then 'SB'
when (wk_status='S' AND log_status='A') then 'Absent (SB)'
when wk_status='N' then 'Weekend'
when bIsHoliday=1 then 'Holiday'
when log_status='A' then 'Absent'
--when mnyLateHrs>0 then 'Late'
when log_status='P' then 'Present'
end)
from tblLogAbsent
where dtAttendate = a.dtAttendate and intEmpCode=a.intEmpCode),

'TIMEIN'=(select dtEmpTimeIn from tblLogStatus where dtAttendate=a.dtAttendate and intEmpCode=a.intEmpCode),

'TIMEOUT'=(select dtEmpTimeOUT from tblLogStatus where dtAttendate=a.dtAttendate and intEmpCode=a.intEmpCode)

from tblLogStatus a
where a. dtAttendate between @.fromDate and @.toDate

group by a.dtAttendate, intEmpCode

========================================================================================

here i use two different table "tbllogabsent" for his attendance status and "tbllogstatus" as a for additional information... this is not becoming problem... if u cant understand any line of code ask me again...hopefully this will be helpfull to u...

|||

Hmmm... I'm not sure how to use this information. In your initial code, you do a SELECT from a table called Attendance, which seems to have columns like Abences, employee_id_fk and Date_Absent. Can you give us more details on this table please? Thanks.

|||

Hi johram,

Thanks for replying. Yes you are right. The attendance table for employees has columns like EmpID_fk, Attendance_Date, ... , Attendance_Total. The Attendance_Total column is dependent on our business rules which include reason for signing in/out. e.g. If an employee has signed out for some official task, 1 is added to his Attendance_Total column. If he is going away for a business tour, 2 may be added to his Attendance_Total column. So in one day, an employee can have more than one record. The records for an employee may look like the following:

EmpID Attendance_Date ... Attendance_Total

1001 04/28/2006 1

1001 04/28/2006 2

1001 04/28/2006 1

So on 28th April, the total for Emp (1001) = 4. There are other columns in there but im only concerned with Attendance_Total. I need to display the SUM (Attendance_Total) for each day in a month for each employee.

If I further elaborate my report based on the above example, it may look something like:

EmpID EmpName D1 D2 D3 D4 D5 D6 ......

1001 ABC 1 0 4 4 1 0

I have tried a few techniques (under my experienceJ), but when it comes to computing the sum for each day, it takes almost 4-5 minutes which I am sure nobody wants. Also this report can be accessed anytime within a day and employees keep coming and going for business, so cant store the records and need to compute them everytime the report is accessed.

Thanks to you all for helping me...

|||

Hi patuary,

Thanks for your reply aswell. I have tried your technique and understand it - i bet :) Anyways, after applying your technique, there are the following two problems:

1. All records are being returned as rows e.g. The records for Emp 1001 in a month is not in a single row, rather seperate rows are returned for each day

2. Also, this query only returns data for days on which attendance may be marked. But if there is a weekend or the employee was absent, his attendance record for that day is not computed as there are no records. Whereas in my case, if he was absent or no record found on a given day, his attendance record must be marked as 0.

Thanks again for your time...

|||

Guys, please provide your valueable feedback...

|||

Is it important that you get a result with all the days, even if the sum is zero? Cause that will make it a bit more complicated in the SQL. You can have the SQL report back all days that actually have a total (greater than zero), and then in your GUI you can render the rest of the days as empty. In that case, I think we can work out a solution for you. At least that's what we'll start with ;-) I'll see what I can do!

Also, what's the datatype of you Attendance_Date column?

|||

If you are using SQL 2005, there should be a new statement called PIVOT, although it is nowhere to be found in the T-SQL reference manual on MSDN. Maybe you are luckier than me ;-) Pivot is the term for when you shift the layout of a table so that you look on it from a different perspective. In this case, you want to pivot the table on the date so that each date represents a column rather than a row.

Now, this can be done with a function calledCrosstable, which was developed by the legendary Rob Volk. The source code for this function can be foundhere. Note that you need to change the column "pivot" to "tpivot" or something, since "pivot" is a keyword in SQL 2005.

This is the modified version of Crosstable that will work in SQL 2005:

ALTER PROCEDURE crosstab @.select varchar(8000),@.sumfuncvarchar(100), @.pivotvarchar(100), @.table varchar(100)ASDECLARE @.sqlvarchar(8000), @.delimvarchar(1)SET NOCOUNT ONSET ANSI_WARNINGSOFFEXEC ('SELECT ' + @.pivot +' AS tpivot INTO ##pivot FROM ' + @.table +' WHERE 1=2')EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot +' FROM ' + @.table +' WHERE ' + @.pivot +' Is Not Null')SELECT @.sql='', @.sumfunc=stuff(@.sumfunc,len(@.sumfunc), 1,' END)' )SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )WHEN 0THEN''ELSE''''END FROM tempdb.information_schema.columnsWHERE table_name='##pivot'AND column_name='tpivot'SELECT @.sql=@.sql +'''' +convert(varchar(100), tpivot) +''' = ' + stuff(@.sumfunc,charindex('(', @.sumfunc )+1, 0,' CASE ' + @.pivot +' WHEN ' + @.delim +convert(varchar(100), tpivot) + @.delim +' THEN ' ) +', 'FROM ##pivotDROP TABLE ##pivotSELECT @.sql=left(@.sql,len(@.sql)-1)SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0,', ' + @.sql +' ')EXEC (@.select)SET ANSI_WARNINGSON

Now, to demonstrate the power of this function I made a quick sample for you to push you in the right direction:

EXECUTE Crosstab'SELECT EmpId FROM Attendance GROUP BY EmpId','SUM(Attendance_total)','Attendance_date','attendance'

This will give you a matrix with all the employees vertically, and horizontally you will have all unique dates, with the respective attendance total for each employee on that day. As I said earlier, this will not give you all the days of the month, unless there are data for each day. So you might need to do some logic in your GUI to render "empty" days correctly. Good luck!

|||

Hi Johram,

Once again thanks for your time. I really appreciate all your help. Yes your rite, we do need to handle all days in a month. But as you mentioned this can be handled in my logic so im lesser concerned about the days without any data.

Anyways, im aware of the Pivot function. Its basically used to convert rows into columns. Indeed the functionality i have in my stored procedure does the same job. What i do is that i have a temporary table in which i insert 31 rows for each employee. so for 500 employee, i insert 500x31 rows. Later i convert the rows into columns and display it. Although i do not use the Pivot function but i did once give it a try and the Processing Time was similar to what i have in there right now.

Still, im not ready to backout and will definitely give a try to your solution. Let me see what can i get out of it. By the way Johram, if you have dealt with any monthly or annual report in the past, usually how much time does it take to display such a report? Do you think that im being over ambitious in displaying such a report or such reports do take their time...

Once again, thanks alot for every help you have provided...

|||

Sorry, haven't done exactly this kind of report before. But it will depend on the amount of data you are trying to cover. Is it relevant to show ALL employees in a list/report? Maybe you should restrict it to region, or last name or something. Try to do a selection out of the 500 if it possible.

Although I havent been able to compare this crosstable thing with your first query, I still think that it might be faster. Try implement it and see for yourself. Good luck!

|||

Hi Johram

Thanks alot for your time and patience. I really appreciate your efforts and the help you have provided.

keep up the good work...