GV

GeezerVison

The World Wide Web, restored to 800 pixels of glory.

Home|About|Gaming cabinet|Archives|How it works|Starter sites

On this site

  • Sign in
  • ActionsCI/CD and automation
  • CodespacesCloud development environments
  • All Features
  • Documentation
  • Training
  • Certifications
  • Build with Copilot
  • Customize Copilot
  • Integrations
  • Trending repos
  • GitHub Sponsors
  • Developer articles
  • Enterprises

This view

  • Original page
  • New address

GeezerVison is showing github.com — Make streamId a sql parameter of type SqlDbType.Char by cheesebridge · Pull Request #197 · SQLStreamStore/SQLStreamStore

This repository was archived by the owner on Aug 15, 2024. It is now read-only.

Conversation

@cheesebridge

@cheesebridge cheesebridge commented Oct 23, 2018

Copy link
Copy Markdown

In our project we saw serious performance issues when having millions of streams.
This issue was caused by a CONVERT_IMPLICIT() function that was always executed when searching streams by streamId.
Our streamId was a regular Guid and the parameter type got translated to a nvarchar(36)
The result was an index scan on the streams table instead of an index seek.

In this PR, all streamId parameters are typed as SqlDbType.Char with length 42 because the streamid column on the streams table is declared as CHAR(42).
Now an index seek can be done.

image

Before
image

After
image

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
rikbosch and nielswitte reacted with thumbs up emoji
All reactions
  • 👍 2 reactions
@cheesebridge
Make streamId a sql parameter of type SqlDbType.Char so no implicit c…
ca0b3e4 
…onversion is done in MsSql when searching for a stream
thefringeninja
thefringeninja approved these changes Oct 26, 2018
View reviewed changes
@thefringeninja

thefringeninja commented Oct 26, 2018

Copy link
Copy Markdown
Contributor

Just waiting on CI now... I had b0rked the build but just fixed it
All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
@thefringeninja

thefringeninja commented Oct 26, 2018

Copy link
Copy Markdown
Contributor

Wow, I can't say for sure but it looks like this PR has shaved a few seconds off of the CI build. Thanks!
All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
@thefringeninja
thefringeninja merged commit 3cd3929 into SQLStreamStore:master Oct 26, 2018
@damianh

damianh commented Oct 26, 2018

Copy link
Copy Markdown
Member

Great stuff
All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
@damianh damianh added the bug label Oct 26, 2018
@damianh damianh added this to the v1.1.3 milestone Oct 26, 2018
@damianh

damianh commented Oct 26, 2018

Copy link
Copy Markdown
Member

I'm going to cherry pick this across to a 1.1.3 release branch.
All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
@damianh damianh mentioned this pull request Oct 26, 2018
Make streamId a sql parameter of type SqlDbType.Char (cherrypicked from #197) #201
Merged
@damianh damianh modified the milestones: v1.1.3, v1.2.0 Oct 26, 2018
damianh added a commit that referenced this pull request Oct 26, 2018
@damianh
Merge pull request #201 from damianh/patch-1.1.3
135a6fb 
Make streamId a sql parameter of type SqlDbType.Char (cherrypicked from #197)
@damianh

damianh commented Oct 26, 2018

Copy link
Copy Markdown
Member

v1.1.3 is now on nuget.org.
All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
@damianh damianh added enhancement and removed bug labels Oct 26, 2018
@damianh

damianh commented Oct 26, 2018

Copy link
Copy Markdown
Member

@cheesebridge awesome first PR. Many thanks!
All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
@yreynhout

yreynhout commented Oct 26, 2018

Copy link
Copy Markdown
Contributor

Good stuff! Not sure we can improve on this but Index Scan is generally not a good thing - we should strive for Index Seeks (but as said, I don't know whether that is possible here).
All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
@cheesebridge

cheesebridge commented Oct 27, 2018

Copy link
Copy Markdown
Author

You're welcome. It's a great product 👍

@yreynhout Well this PR turns the index scan into an index seek as you can see in the 'after' screenshot of the query execution plan. ;-)
All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
@yreynhout

yreynhout commented Oct 27, 2018

Copy link
Copy Markdown
Contributor

My bad 😂
All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.

Reviewers

1 more reviewer
@thefringeninja thefringeninja thefringeninja approved these changes
Reviewers whose approvals may not affect merge requirements

Uh oh!

There was an error while loading. Please reload this page.

Assignees

No one assigned

Labels

enhancement

Projects

None yet

Milestone

v1.2.0

Development

Successfully merging this pull request may close these issues.

Uh oh!

There was an error while loading. Please reload this page.

4 participants

@cheesebridge @thefringeninja @damianh @yreynhout
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
You are visitor 01402503 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.