July 11, 2008 at 11:20 am
I think that you might have the wrong idea, Mike. Like Lynn, I started to look at this a couple of days ago, but after an hour or so I got stuck because there were just too many things that I didn't know.
What you are asking for is not easy. We are happy to provide as we can, but there is no cut and dried way to convert a heavily procedural, cursor-based routine like this one into a set-based routine; it's just hard work. And they bigger they are, the harder they are. As I said, we are happy to do it, but we have to fit it in with everything else in our lives, including helping other people. What Posters can do to make it easier for us (and thus more likely that we will get through it all), is to prepare everything for us that they already have, or can provide.
So the bigger and/or harder a problem is (and this one is pretty hard), the more important it is to provide these things. All we're asking is for you to make it easier for us to help you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 11, 2008 at 11:31 am
help
July 11, 2008 at 1:53 pm
CTEs are much easier to use after you get use to them. The scripts I have ran using cursors run MUCH faster when using CTEs.
July 11, 2008 at 1:57 pm
Donald L Berndt II
can i expect any help from you regarding my store proc. I guess,am not able to use CTE's in the right way?
July 11, 2008 at 2:12 pm
There's a table called dbo.InspectionItems mentioned frequently in the script, which isn't included in the table definitions anywhere that I can find.
What's the structure, and some sample data, for that table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 2:20 pm
Here it is
TAble Inspectionitems
CREATE TABLE [dbo].[InspectionItems](
[ItemId] [int] IDENTITY(1,1) NOT NULL,
[TypeId] [int] NULL,
[FloorId] [int] NOT NULL,
[ProgramInstanceId] [int] NULL,
[InspectionItemName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ZoneInstanceId] [int] NULL,
[Active] [bit] NULL CONSTRAINT [DF_InspectionItems_Active] DEFAULT (1),
[ScoreTypeId] [int] NOT NULL CONSTRAINT [DF_InspectionItems_ScoreTypeId] DEFAULT (1),
CONSTRAINT [PK_InspectionItems] PRIMARY KEY CLUSTERED
(
[ItemId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Sample Data
11111EntranceNULLFalse3
21111EntranceNULLFalse3
3445StairwellsNULLFalse3
4445Stairs-AllNULLFalse3
8545Restrooms-AllNULLFalse3
July 11, 2008 at 2:44 pm
Are IX_SubInspectionTypes and SubInspectionTypes tables, or something else?
Also, with the sample data provided, the cur_InspectionGroup cursor ends up with no rows. I'll see if I can figure enough out to create some sample data that will work for this, but I might not be able to.
What is the business purpose of this proc? What does it do, in human terms? I'm gathering, from the structure, that it goes through a history of inspects, and derives a running total (or some such) of the "scores" from the inspections. Is that correct?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 2:47 pm
exactly.
also you can check the result set i have posted.
July 11, 2008 at 2:51 pm
Can you give me a table structure for those last two tables? I think they're the last ones I'm missing. (And sample data, of course.)
I think I can simplify the this pretty majorly. Just breaking down the internal functions is a bit complex.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 2:58 pm
I had a little time here at work and started looking at your code a bit. Something I noticed was the use of 3 part naming conventions in your code: schema.table.column in your select statements and join conditions. This is being depreciated starting with SQL Server 2008 (if I remember correctly), so you may want to start working more with table aliases and using 2 part naming conventions in your select statements and join statements.
Other than that, I haven't had a lot of time myself. Seeing that GSquared is looking at it, I'm sure he'll come up with something very performant.
I'll still look at it myself when I gete more time.
😎
July 11, 2008 at 2:59 pm
Here it is
CREATE TABLE [dbo].[IX_SubInspectionTypes](
[ItemId] [int] IDENTITY(1,1) NOT NULL,
[SubInspectionTypeId] [int] NULL,
[InspectionItemId] [int] NULL,
[Active] [bit] NULL,
CONSTRAINT [PK_IX_SubInspectionTypes] PRIMARY KEY NONCLUSTERED
(
[ItemId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
209True
134064True
12910934True
1044504314True
1445905988True
1437806012True
CREATE TABLE [dbo].[SubInspectionTypes](
[SubInspectionTypeId] [int] IDENTITY(1,1) NOT NULL,
[SubInspectionTypeName] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RoomAreaTypeId] [int] NULL,
[Active] [bit] NULL,
CONSTRAINT [PK_SubInspectionTypes] PRIMARY KEY CLUSTERED
(
[SubInspectionTypeId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
1Production Areas7True
2Warehouse4True
3Restrooms5False
4Locker Rooms5False
July 11, 2008 at 4:50 pm
Lynn Pettis
yeah i'll change the naming conevention, actually it was written by some one else long back in 2000, now since they migrated to 2005, i have to work on this.
thanks
July 11, 2008 at 5:05 pm
Bijal Parekh (7/10/2008)
You can try using while loop where cursor is used
Why? What do you think a cursor is, anyway? It's nothing but an internal table with a While loop.
The real question is why would anyone think they'd expect to get an answer for 219 lines of computational hell that has virtually no documentation? 😉 Do the same thing we'd have to do... divide and conquer...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2008 at 8:30 am
I took a look at the code, and I need to know if this is a valid statement of the relationships between the various tables:
select *
from dbo.Audits
inner join dbo.Audits_InspectionItems
on Audits.AuditID = Audits_InspectionItems.AuditID
inner join dbo.Audits_ItemScore
on Audits_InspectionItems.ItemID = Audits_ItemScore.ItemID
inner join dbo.InspectionItems
on Audits_InspectionItems.ItemID = InspectionItems.ItemID
inner join dbo.Audits_Comments
on Audits_InspectionItems.ItemID = Audits_Comments.AuditInspectionItemID
left outer join dbo.IX_SubInspectionTypes
on IX_SubInspectionTypes.InspectionItemId = Audits_InspectionItems.ItemID
left outer join dbo.SubInspectionTypes
on IX_SubInspectionTypes.SubInspectionTypeId = SubInspectionTypes.SubInspectionTypeId
where Audits.AuditID = @AuditID
Does that get ALL the data for the current audit? Once I'm sure of that, I'll work on the prior audit.
Also, I need to know if I'm correct that you only use the data from the immediately prior audit, not from all prior audits of the same ItemID? (I'm asking because of all the "Select Top 1" statements.)
If that select statement is correct, this becomes a very simple union query. If the select statement is correct, and you do use all prior audits instead of just one, then it requires a union and a running total, which is microscopically more complex, but still not that big a deal to put together.
Just need answers on these questions before I can continue.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 8:32 am
I also don't have definition and data for dbo.Audits_Comments.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply