April 28, 2009 at 10:37 am
Hello,
Our environment has several application-specific databases, and a couple of shared 'reference' databases.
Some of the reference lists in the ref-Databases are 'Application-specific' - when calling for the list we pass an indicator (AppID).
Until recently we had just hard-coded the proper indicator into the various SPs, but it's grown tiresome to hand-tune the SPs depending on the App database they're in; I want to recode the calls to use a 'constant' that pertains to the calling database.
So far:
1) I thought to use the database extended properties, but apparently you have to allow the user-account priviliges to meta data in general. So no, that wont happen,
2) I thought to use a UDF that returns the value, and we'd have to customize the one function for each database, but that'd be okay. Then I read that UDFs in a where clause could slow things down, and that even local variables in an SP could cause optimization problems.
3) my next step would be to create a table with just the AppID, then write a subquery into the WHERE clause when needed. But that seems like a LOT of overkill/overhead...
Is there a better approach to defining a database-specific, persistant, accessible value?
Thanks in advance,
Cheers,
Mark
Mark
Just a cog in the wheel.
May 13, 2009 at 6:09 am
In a SQL Server database you basically store data in tables, so using a table for this kind of storage seems like the most logical solution.
A couple of hints: familiar interface, 'integrated' security, indexes...
The overhead will only be noticed during development. In production (including multiple deployments and updates/upgrades) the overhead will probably not be noticable at all.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 13, 2009 at 9:06 am
Matija Lah (5/13/2009)
In a SQL Server database you basically store data in tables, so using a table for this kind of storage seems like the most logical solution.A couple of hints: familiar interface, 'integrated' security, indexes...
The overhead will only be noticed during development. In production (including multiple deployments and updates/upgrades) the overhead will probably not be noticable at all.
Hi Matija,
Thanks for the replay. Yes, 'data' is stored in tables, and that's where we'll probably end up putting the value. I was hoping to enhance query speed by simplifying the query construct (less tables = less for the query optimizer to think about). It's a simple, single value associated to a database. Not much to index on.
My background in other programming languages biases me towards using 'contants' when a value is known and to be unchanging. Ah well, such is life.
Not sure what you mean by 'The overhead will only be noticed during development'. the query plans will probably be similar without regard to the particular server...
Cheers and thanks: I'll look at the resultant plans a bit more,
Mark
Mark
Just a cog in the wheel.
May 13, 2009 at 10:09 am
If I had to choose between (i) maintaining multiple copies of practically identical modules containing pre-defined customized data (making them in effect not equal) and (ii) maintaining multiple copies of completely identical modules capable of configuring themselves at run time, I would always go for the latter option.
(In fact, a system I'm working on at the moment is using a central "knowledge base" for multiple deployments. Via web services, even.)
If you're worried about performance then you can still reduce resource usage by maintaining a central database containing all configuration options, and propagate a specific subset of these settings to each individual production database. This way you can keep a small(er) set of configuration data in the local database without the need for cross-database (or cross-server) queries. I assume here that changes to the configuration data for a specific deployment are rare (but when they do happen one would like to avoid having to also modify the modules).
IMHO, the most important aspect in this case is the fact that all code can be maintained in one place. Consider using an automated tool to find differences between the objects of two different databases.
When referring to 'the overhead in development' I meant the need to design this additional configuration functionality which otherwise would not be needed.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 13, 2009 at 11:04 am
Matija Lah (5/13/2009)
If you're worried about performance then you can still reduce resource usage by maintaining a central database containing all configuration options, and propagate a specific subset of these settings to each individual production database.
Hi again, and thanks for the perspective.
Our shop is really pretty small, and the databases are not being replicated & maintained all over the place. We have a few (10 or so) 'applications', each being fed by a particular database; each also 'sharing' certain data for interoperability, and with a central database for pan-application data. I've been working on databases for 12 years, and on this company's projects for 9. I just thought I saw an oportunity to tweak the performance a bit, by cutting out a table reference or a scalar-function call (which I've read the Query Optimizer cannot really optimize).
We *are* talking reference lists here, not millions of rows of data. It's really not a big deal.
I'ts an on-going process, and i was just looking to add other methods to my toolbox. i don't plan on rebuilding working SP, rather, through attrition to introduce more effective methods.
Cheers and thanks from Denver,
Mark
Mark
Just a cog in the wheel.
May 14, 2009 at 7:08 pm
[font="Verdana"]If a value can change... it's not a constant.
Stick it in a table. Cache the table. You can use tricks like local caches stored in compact edition databases, refreshed on start up, or a web service or middle tier business object caching the values. But even if you read the values raw from the database each time, it's still going to be quick.
[/font]
May 14, 2009 at 7:38 pm
One way to do this would be to use the db_name() function. You don't need elevated privileges to run it either.
I've only done very limited testing with this, but it is giving me the name of the database I am currently connected to, even when I run a query that access a different database.
May 14, 2009 at 8:53 pm
If you have a *very* small number of constants and their speed is *really* important, then you could make a constant scan in a View or an inline Table-Valued function.
A single value:
Create View ThisDBsAppID
As
Select 27 as AppID
Multiple Values:
Create View DBAppParams
As
Select 'AppID' as [ParamName], 27 as [ParamValue]
UNION ALL Select 'ClientID', 13
UNION ALL Select 'FormSections', 4
[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]
May 15, 2009 at 8:29 am
Bruce W Cassidy (5/14/2009)
[font="Verdana"]If a value can change... it's not a constant.Stick it in a table. Cache the table. You can use tricks like local caches stored in compact edition databases, refreshed on start up, or a web service or middle tier business object caching the values. But even if you read the values raw from the database each time, it's still going to be quick.
[/font]
Hi Bruce,
Yes, thanks for the note. The value is different for different databases, but *always* the same within that database and when used to call out to the central database.
I'll run some comparative tests... I really have no speed issues at this time, rather was just looking for the 'best' way to feed the value through cross-database SPs calls...
Cheers,
Mark
Mark
Just a cog in the wheel.
May 15, 2009 at 8:41 am
Glenn Dorling (5/14/2009)
One way to do this would be to use the db_name() function. You don't need elevated privileges to run it either.I've only done very limited testing with this, but it is giving me the name of the database I am currently connected to, even when I run a query that access a different database.
Hi Glenn,
Yes, thanks for the note.
Our central database uses an integer indicator for Reference list applicablility. While using varchars for keys is workable, they really are slower than numeric values, and so we've elected to use integers. (Actually, for those not faint-of-heart, we're using the 32 bits that comprise the integer as individual flags for the ref-item's applicability. For example, suppose App A is disignated 1, App B is 2, App C is 4, &c. So if a ref item is applicable to both App A and to App C, then its App-ID reference value = 3; we do a bitwise comparison vis., when calling from App A: (AppKey & 1) > 0 will pull the ref items.)
Cheers,
Mark
Mark
Just a cog in the wheel.
May 15, 2009 at 8:56 am
RBarryYoung (5/14/2009)
If you have a *very* small number of constants and their speed is *really* important, then you could make a constant scan in a View or an inline Table-Valued function.A single value:
Create View ThisDBsAppID
As
Select 27 as AppID
Hi Barry,
Thanks for the note. Speed is *always* important , but should always be balanced against effort (development time)...
At this time I'm considering only the one, single value that is passed from the application database into the central database (reference tables and such).
I actually had a variant of your sulution in mind at the start, initally wanting to feed the value from an extended property - set on the database itself, thereby making the view exactly the same in all application-databases.
Cheers and thanks,
Mark
Mark
Just a cog in the wheel.
May 15, 2009 at 9:03 am
starunit (5/15/2009)
RBarryYoung (5/14/2009)
If you have a *very* small number of constants and their speed is *really* important, then you could make a constant scan in a View or an inline Table-Valued function.A single value:
Create View ThisDBsAppID
As
Select 27 as AppID
Hi Barry,
Thanks for the note. Speed is *always* important , but should always be balanced against effort (development time)...
At this time I'm considering only the one, single value that is passed from the application database into the central database (reference tables and such).
I actually had a variant of your sulution in mind at the start, initally wanting to feed the value from an extended property - set on the database itself, thereby making the view exactly the same in all application-databases.
Messing around with extended properties is not going to be as fast as any of the other methods here, and certainly not as fast as the constant scan method that I demonstrated.
[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]
May 15, 2009 at 10:11 am
RBarryYoung (5/15/2009)
Messing around with extended properties is not going to be as fast as any of the other methods here, and certainly not as fast as the constant scan method that I demonstrated.
Hi Barry,
I'm sure you're right. I just hadn't run the analysis tools against the various approaches (I know: I'm remiss in that regard). And, the extended property quickly turned into a dead-end - it required the user-connections to have access to meta-data...
Cheers and Many thanks,
Mark
Just a cog in the wheel.
May 15, 2009 at 10:11 am
Why not just create a view in each database with the application ID hard coded in that view:
Create view vAppID as select AppID = 12345
Then use vAppID.AppID in all your selects, instead of the hard coded value. Since you would only have to change the one view, none of the other code would have to change from one database to the next.
Another alternative would be to create views on all you lookup tables with the AppID hard coded in each one:
create view vLookupTable as select * from RefDb.dbo.LookupTable where AppID = 12345
If you go that way, then I would write a stored procedure to create all the needed views, so that you can just pass the proper AppID value and have it create all the views. This might actually be the most efficient way to handle it in terms of performance, since the query optimizer can see the value you are selecting on.
Then you would just need to run this statement once to create all the lookup views:
execute CreateLookupViews @AppID = 12345
May 15, 2009 at 11:01 am
starunit (5/15/2009)
Glenn Dorling (5/14/2009)
One way to do this would be to use the db_name() function. You don't need elevated privileges to run it either.I've only done very limited testing with this, but it is giving me the name of the database I am currently connected to, even when I run a query that access a different database.
Hi Glenn,
Yes, thanks for the note.
Our central database uses an integer indicator for Reference list applicablility. While using varchars for keys is workable, they really are slower than numeric values, and so we've elected to use integers. (Actually, for those not faint-of-heart, we're using the 32 bits that comprise the integer as individual flags for the ref-item's applicability. For example, suppose App A is disignated 1, App B is 2, App C is 4, &c. So if a ref item is applicable to both App A and to App C, then its App-ID reference value = 3; we do a bitwise comparison vis., when calling from App A: (AppKey & 1) > 0 will pull the ref items.)
Cheers,
Mark
Using a bit mask is a nice and easy solution and since you are talking about so few rows performace should be fine. But, you do realize that:
1. You a limited to 63 Apps (assuming BIGINT).
2. It is not Sargable, so even though it is Numeric data Textual data will be faster since it can take advantage of an index.
Just an FYI..
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply