Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Export-DbaUser is much faster than Get-DbaUser, even though it does less work. #9450

Open
ReeceGoding opened this issue Aug 21, 2024 · 2 comments · May be fixed by #9623
Open

Export-DbaUser is much faster than Get-DbaUser, even though it does less work. #9450

ReeceGoding opened this issue Aug 21, 2024 · 2 comments · May be fixed by #9623
Labels
bugs life

Comments

@ReeceGoding
Copy link
Contributor

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

No error, but this doesn't feel like intended behaviour. If I want to get all users for a particular login, Get takes minutes. If I want to export the scripts to create them as well as their extra permissions, Export takes seconds. Logically, Export ought to require getting each user, so this mismatch in runtimes seems very strange.

Steps to Reproduce

Just run both for a particular login on one of your boxes. No special parameters or steps needed. Don't specify a database. It might be worth mentioning that my instances all have around 50 databases.

Please confirm that you are running the most recent version of dbatools

Yes.

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

5.1.19041.4648

SQL Server Edition and Build number

I hit multiple, but mostly 2019.

.NET Framework Version

Very recent.
@ReeceGoding ReeceGoding added bugs life triage required New issue that has not been reviewed by maintainers labels Aug 21, 2024
@PowerDBAKlaas
Copy link
Member

At first sight I think the "try Connect-DbaInstance " is the culprit. I've found this in multiple commands.
It makes functions incompatible with Azure while those otherwise could work fine, and I believe it makes them slower as well.
A general approach to make embedded Connect attempts faster and avoid redundant connections will be preferred.
(for Get-DbaDbUser in particular, other adjustments may also be beneficial, but I believe those to be of minor influence)

@niphlod
Copy link
Contributor

niphlod commented Mar 12, 2025

nope @PowerDBAKlaas , it's not.

Get-DbaDbUser does

$databases = $server.Databases | Where-Object IsAccessible

if ($Database) {
    $databases = $databases | Where-Object Name -In $Database
}
if ($ExcludeDatabase) {
    $databases = $databases | Where-Object Name -NotIn $ExcludeDatabase
}

Export-DbaDbUser does, instead

foreach ($instance in $SqlInstance) {
    $InputObject += Get-DbaDatabase -SqlInstance $instance -SqlCredential $SqlCredential -Database $Database -ExcludeDatabase $ExcludeDatabase
}

Both then do $db.Users for each db.

I guess here the point is that with 50 databases, targeting just one db, the raw query Get-DbaDatabase to prefilter dbs does is simply faster than SMO enumeration.

If you try to clock Get-DbaUser vs Export-DbaUser NOT targeting a specific db, Get-DbaUser is actually a lot faster.

@niphlod niphlod removed the triage required New issue that has not been reviewed by maintainers label Mar 12, 2025
@niphlod niphlod linked a pull request Mar 12, 2025 that will close this issue
Open
10 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bugs life
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Get-DbaDbUser, faster enumeration when targeting single db niphlod/dbatools
3 participants
@niphlod @PowerDBAKlaas @ReeceGoding