Matrix multiplication without cursors?

Question

Matrix multiplication without cursors?

Viewing 11 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Another problem that I am having is that the latest version of your Proc is making reference to Tables in a Pathfinder database, that you have not given us. These include:

-- Pathfinder.dbo.Respondents

-- Pathfinder.dbo.AllScores

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

OK, I've looked through it. As I mentioned before, I cannot really test it or try any performance improvements, because I do not have everything that it needs to run. However, I do have several recommendations...

First, the only cursors left are for parsing your CSV parameters. That's good, but you can get rid of them too. See Jeff's article here[/url] for detailed discussion of how to do this.

Secondly, I notice that you are using Table variables to hold all of your intermediate table information. I recommend that you switch these to temporary tables because the query optimizer can get statistics on these and produce better execution plans.

Thirdly, you should put primary keys on these temporary tables.

Finally, you should also add indexes to these temporary tables to match the columns that you are JOINing on. If you do these things and then get back to us, we can take a look at the Execution Plan and make some further recommendations from there.

I think that there is a lot of room for performance improvement here and realistically a routine like this should be able to run in seconds, not minutes.

[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]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

RBarryYoung (5/9/2009)
OK, I tried downloading it and then opening it in notepad (in addition to my usual of opening it in another Firefox tab). Both of these came out fine for me, Jeff.

OK... Downloading it did the trick for me. Just opening it in an IE tab didn't do it for me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Jeff Moden (5/10/2009)
RBarryYoung (5/9/2009)
OK, I tried downloading it and then opening it in notepad (in addition to my usual of opening it in another Firefox tab). Both of these came out fine for me, Jeff.
OK... Downloading it did the trick for me. Just opening it in an IE tab didn't do it for me.

Yeah, stuff like this is why I use four different browsers :angry:

I find that Firefox usually works best on SQLServerCentral. For instance, I don't have any trouble cutting and pasting from the code windows with it, it is still coming out formatted correctly.

[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]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Heh... There're a lot of folks that really only have one option for a browser. And, as far as I know, there are still more people in the world that use IE than FireFox or any other browser and it's to them some modicum of consideration should be forwarded. IE is certainly not the best but, then, neither was VHS. 😛

Everything was working just fine before they tried to get fancy. I wish they'd just put it back the way it was. I'll put up with the occasional smiley face and not being to post (ugh!) XML rather than have some poor slob trying to copy some otherwise beautiful code from the forum and have it come out looking like hell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Jeff Moden (5/10/2009)
And, as far as I know, there are still more people in the world that use IE than FireFox or any other browser ...

Nope, not any more: http://www.w3schools.com/browsers/browsers_stats.asp

[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]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Heh... "Must look eye". That's just for the people that hit the W3Schools website... not what the rest of the world is

doing. Even W3Schools acknowledges that when they included the following fine print below the

grids...

W3Schools is a website for people with an interest for web technologies. These people are more

interested in using alternative browsers than the average user. The average user tends to use

Internet Explorer, since it comes preinstalled with Windows. Most do not seek out other browsers.

These facts indicate that the browser figures above are not 100% realistic. Other web sites have

statistics showing that Internet Explorer is used by at least [font="Arial Black"]80%[/font] of the users.

It would be more interesting to see what some of the ISP's have to say about their customers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

In fact... here's a couple of other views concerning world wide browser usage...

http://www.thecounter.com/stats/2009/March/browser.php

http://www.upsdell.com/BrowserNews/stat.htm

http://en.wikipedia.org/wiki/Usage_share_of_web_browsers#Present_to_1999

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PhilM99 SSCrazy Points: 2214 More actions · Answer 9

Thanks for your assistance. I took Mother's day off to go feed the blackflies in Northern Ontario.

Some replies and questions:

Sorry for the difficulties downloading my code. I tried to follow the instructions.. I saved the .sql generated by SSMS but the forum does not permit that as a file type for upload to I copied it to .txt and uploaded it. What should I have done?

To RbarryYoung.. I am testing the latest version (posted a few replies ago) with the following:

EXEC [GetKTAnalysis_Summary_new_4] '1698,1699', '5,48,61'.

I will check to see if I have posted that version. Main difference is that I have eliminated the CSV parsing of the large parameter, and dropped intermediate cursors which I was processing RBAR, by making them into derived tables (am I explaining that correctly?). Yes the data is available in a table in another db. Due to limitations of my skill and the programming I was using (VB, ASP) (and the heritage ..MSAccess), I had to pass the parameter as a string. It just kept getting larger as they threw more requirements at me. I am now calling the proc using ASP.Net, but testing using SSMS.

Thanks for the observations re table indices and temp tables versus table variables. Will switch to that and try that and report for your further comments.

Sorry that I failed to include the tables from the other db.. I did not need those at first as I explained and now I need to include them in my testing script. One of them (respondents) is only needed if the calling program wants to do 'all' respondents, as described by calling in with a '0' for respondent number. I think I will comment that out for this discussion.

I will politely decline to comment on the browser wars.

Thanks Phil

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

PhilM99 (5/11/2009)
Sorry for the difficulties downloading my code. I tried to follow the instructions.. I saved the .sql generated by SSMS but the forum does not permit that as a file type for upload to I copied it to .txt and uploaded it. What should I have done?

AFAIK, my difficulty was not downloading your code, but rather that your later, improved versions were referencing tables and databases that you had not provided, thus I could not test it.

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Phil:

OK, left us know if you want us to look at anything else.

[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]