Showing posts with label second. Show all posts
Showing posts with label second. Show all posts

Thursday, March 29, 2012

Cursor Tuning Help!

This cursor is processing one row per second, all it is doing is appending a value to a column. There are 847,000 rows and I can't leave the query to run for 10 days! help!

CREATE procedure [dbo].sp_USR_TEMP_SESSIONAL_ATTENDANCE_APPEND
as

begin

DECLARE @.I_UPN varchar(13)
DECLARE @.I_SURNAME varchar(50)
DECLARE @.I_FORENAME varchar(50)
DECLARE @.I_DOB datetime
DECLARE @.I_GENDER varchar(1)
DECLARE @.I_LEA varchar(3)
DECLARE @.I_DFES varchar(4)
DECLARE @.I_ATTEND_YEAR varchar(4)
DECLARE @.I_WEEK_BEGINNING datetime
DECLARE @.I_ATTEND_CODES varchar(14)

declare @.cnt int

set nocount on

declare cur CURSOR

for select upn,
surname,
forename,
dob,
gender,
'353' as lea,
dfes,
attend_year,
week_beginning,
attend_codes
from tmpATTEND_IMPORT

for read only

open cur

fetch from cur into
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,
@.I_ATTEND_CODES

truncate table tmpATTEND_IMPORT_APPENDED

while @.@.fetch_status = 0
begin
set @.cnt = (select count(*) as cnt from tmpATTEND_IMPORT_APPENDED
where upn = @.I_UPN
and surname = @.I_SURNAME
and forename = @.I_FORENAME
and dob = @.I_DOB
and gender = @.I_GENDER
and lea = @.I_LEA
and dfes = @.I_DFES
and attend_year = @.I_ATTEND_YEAR)

if @.cnt = 0 insert tmpATTEND_IMPORT_APPENDED(
upn,
surname,
forename,
dob,
gender,
lea,
dfes,
attend_year,
week_beginning,
attend_codes)
values (
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,
@.I_ATTEND_CODES)

else update tmpATTEND_IMPORT_APPENDED
set attend_codes = attend_codes+@.I_ATTEND_CODES
where upn = @.I_UPN and
surname = @.I_SURNAME and
forename = @.I_FORENAME and
dob = @.I_DOB and
gender = @.I_GENDER and
lea = @.I_LEA and
dfes = @.I_DFES and
attend_year = @.I_ATTEND_YEAR

fetch next from cur into
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,
@.I_ATTEND_CODES

end

close cur

deallocate cur

end
GO

I tried adapting a post resolved on Romanian SQL Server User Group (www.sqlserver.ro) , to concatenate the columns

DECLARE @.MyTable TABLE (ID INT PRIMARY KEY, VAL VARCHAR(64))

INSERT INTO @.MyTable VALUES (1, 'Cristian')

INSERT INTO @.MyTable VALUES (2, 'Sorin')

INSERT INTO @.MyTable VALUES (3, 'Narcis')

DECLARE @.Lista VARCHAR(MAX)

SET @.Lista = '';

SELECT @.Lista=@.Lista + VAL + ' ' FROM @.MyTable

SELECT RTRIM(@.Lista) AS Lista

GO

-- Output

Lista

Cristian Sorin Narcis

so try the following procedure :

CREATE procedure [dbo].sp_USR_TEMP_SESSIONAL_ATTENDANCE_APPEND
as

begin

DECLARE @.I_UPN varchar(13)
DECLARE @.I_SURNAME varchar(50)
DECLARE @.I_FORENAME varchar(50)
DECLARE @.I_DOB datetime
DECLARE @.I_GENDER varchar(1)
DECLARE @.I_LEA varchar(3)
DECLARE @.I_DFES varchar(4)
DECLARE @.I_ATTEND_YEAR varchar(4)
DECLARE @.I_WEEK_BEGINNING datetime
DECLARE @.I_ATTEND_CODES varchar(14)


set nocount on

declare cur CURSOR

//select only distinct rows

for select upn,
surname,
forename,
dob,
gender,
'353' as lea,
dfes,
attend_year,
week_beginning,
from tmpATTEND_IMPORT
group by upn,surname,forename,dob,gender,lea,dfes,attend_year

for read only

open cur

fetch from cur into
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,


truncate table tmpATTEND_IMPORT_APPENDED

while @.@.fetch_status = 0
begin
//adding to @.I_ATTEND_CODES only rows that respect where clause
select @.I_ATTEND_CODES=attend_codes+@.I_ATTEND_CODES
from tmpATTEND_IMPORT
where upn = @.I_UPN and
surname = @.I_SURNAME and
forename = @.I_FORENAME and
dob = @.I_DOB and
gender = @.I_GENDER and
lea = @.I_LEA and
dfes = @.I_DFES and
attend_year = @.I_ATTEND_YEAR

// and insert what it find and add

insert tmpATTEND_IMPORT_APPENDED(
upn,
surname,
forename,
dob,
gender,
lea,
dfes,
attend_year,
week_beginning,
attend_codes)
values (
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,
@.I_ATTEND_CODES)

fetch next from cur into
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,


end

close cur

Tuesday, March 20, 2012

Current date Prior year date

In details two seperate formulas for date (current range ie: 4/1/06 to 4/30/06), second formula for prior year same month and day range. Grouping is daily.
The formulas work but are seperated by range not picked. How do I get the date ranges side by side for comparison. I am not using cross-tab.
Any help always appreciated.

ok, after starting from square one I finally have the data I was looking for but....
formatting and suppression not doing as I had hoped.
The report looks like this:

Column1 Column2 Column3 Column4 Column5
Current date data data Prior Year data
4/1/05: Fri 200
4/2/05: Sat 47
4/1/06: Sat 247
4/3/06: Mon 30

How do I get these to line up in the same row without subreports?
I have suppressed everything I can think of...

HelpCan you post some sample data and the result you want?

Wednesday, March 7, 2012

Cube with more that one facts Table

Hello,

I made an illustration, that describes the szenario. There is one Master, one dimension and a second facts table. The Master Table holds up all objects. Sometimes I have additional Information for some objects in Master Table. They are stored in extra tables like the table Optional_facts_for_villas.

My question: How can I combine these two fact tables in Cube Designer without doing a Full outer Join in my Database?

The reason why I want it this way, not all optional_fact_tables are necessary now for OLAP, but maybe later. So, I thought, easiest way would be, adding them later to the cube, without changing the database.
Sorry for my english, its not the best :)

SilasYou forgot to mention which version of SQL Server you are using. As far as I remember this should be possible by using SQL Server 2005, but not SQL Server 2000.|||Ok, it's 2000. I think I will use the full outer join ;)