Tag Info

New answers tagged

0

The first thing to do is to look at the query cost and in particular what parts of it are costly. You may not have access to this information and getting it depends on the RDBMS. In the absence of that data try the following:- I'm guessing "personal" is your scratch area so the first obvious step is to index personal.New_HHS HHID. You should be able to ...


0

People are not really criticizing just telling you it is not a good idea. WCF is an alternative. It is not like exposing the database to the internet - it is exposing the database to the internet. Ports are locked down for a reason. It is not just a security risk in general but clearly you would be violating your security policy. The person / group ...


1

You don't say anything about what kind of 'simple tools' you are talking about, so you just get knee-jerk answers based on what people assume it to be. If the tools are desktop apps distributed to untrusted users over the internet, then obviously it would be an incredible bad idea to allow them to execute arbitrary sql on your database server. On the other ...


0

In SQL there are many times where different syntax do the same thing At just one static insert then no difference Clearly the limit of values is static (hard coded) values You cannot reference a table A select statement can be used to present static (hard coded) value as you have shown. And you can even combine hard coded with columns in a select ...


1

Enums are not only faster to insert, delete, update and read. They also take up less disk space, and most importantly: they ensure that you can only store a limited set of values. If you create a field, and you only want to allow values "male" or "female", then an enum is handy to make sure that nobody stores "man". Text fields are just a bad solution to ...


3

Actually, internally the database is not going to store your string representation of your enum in every record. It is effectively making a lookup table of it's own and translating the stored integer value to string (and back) when you interact with the database... From: http://dev.mysql.com/doc/refman/5.0/en/enum.html If you retrieve an ENUM value in a ...


3

The problem is not space. Because space is cheap. It really takes a good book to explain what is wrong with repeating texts in a column, but it's a bad idea. You will get it when you read about database normalization. Database normalization is what strong typing is for programming languages. You can live without, especially for very small projects, but ...


0

Introducing an extra service tier always have a cost in complexity and performance overhead. There are some specific kinds of architecture where introducing a shared service tier (like a REST API) may improve perforce due to shared caching - but it sounds like it is not the kind of architecture you have. Consider an architecture where you have multiple web ...


1

You can take a look at the tran_locks for an exclusive lock (due to the write access). If the lock is done, everything should be fine. select * from sys.dm_tran_locks where resource_database_id = DB_ID(N'YOUR DB') AND request_session_id = [YOUR SESSION] Another idea is to take a look at the requests and check if your last request is still running or ...


0

It depends. Obviously, the more layers in your code the slower it goes. But... there comes a point where direct end-to-end performance doesn't matter as much as scalability. If you have 1 user accessing your database on a local PC, it can go fast. If you have a thousand users accessing the same DB on the same PC, chances are you're going to see them all ...


0

If find it hard to answer this question. The correct general answer should be: it depends. The way I see it with REST: 1. You make an object in your code to call the REST method 2. Call http method 3. Code inside your REST API queries the database 4. Database returns some data 5. REST API code packs up the data into Json and sends it t your client 6. ...


2

If you're concern is speed, then yes a Rest service will be slower for the reasons stated above. However, speed of the type you describe is rarely the primary concern and if it is, can be addressed in other ways. Premature optimisation is the root of all evil. Consider if your primary concern is interoperability (mobile, web, B2B), now REST is very ...


8

When you add complexity the code will run slower. Introducing a REST service if it's not required will slow the execution down as the system is doing more. Abstracting the database is good practice. If you're worried about speed you could look into caching the data in memory so that the database doesn't need to be touched to handle the request. Before ...


0

I don't know where you get lost, but it is pretty clear, when you're using REST API you are doing extra step, and extra step "always" mean slower when programming involved. There's pros and cons, but if you can access database directly from your application it always better to call it directly instead of using Web API, of course if you use Web API you can ...


5

SQL is based on Relational Algebra and Tuple Relational Calculus, not higher-order functions or functional programming. While SELECT, FROM and WHERE have analogous functions in other languages, SQL itself doesn't support generalized higher order functions, but only those "higher-order" functions that the language itself defines. Since SQL doesn't allow ...


3

Have a look at LINQ, which takes the basic concepts behind SQL and generalizes it to object-oriented programming. The Where operator is a bog-standard Filter, the Select operator is a projection/Map, and so on. All of the basic SQL query operations are represented in LINQ, implemented using higher-order functions, so yes, you're correct in your intuitive ...


1

SQL Server has a feature called query notification. The application submits a query and some parameters. This is know as a subscription. If data changes so the result of this query would be different with the new data, the notification fires. From BoL: Query notification subscriptions must be created from a database access interface. Transact-SQL does ...


0

It depends if you think in terms of sets and formal logic..... If you do then not using the "join" keyword makes for more simple progression from formal logic to SQL. But if like 99% of people, you did not enjoy formal logic in your maths degree, then the join keyword is a let easier to learn. SQL used to be presented at university as just anther way to ...


2

You have to have knowledge of whether any given user has voted on any given post and on any given comment. This means that you have to introduce a new user_post_votes table with a user_id column and a post_id column, as well as a user_comment_votes table with a user_id column and a comment_id column. If it is only possible to upvote, then the presence of a ...


1

At a conceptual level, yes, a view is just a stored query that the optimizer will combine with whatever additional joins and predicates you add in your code to produce an optimal query plan. In theory, using a view should be no different than replicating the "monster query" over and over in your code. And to the extent that your DBA team may be able to do ...


1

In general, a view's performance is equal to that of the defining query's performance. Usually the query planner can do all its tricks across views' boundaries if views are joined with other datasets. It's always a good idea to run EXPLAIN PLAN against your most used statements, and see what might be a bottleneck. Views that perform costly processing can ...


1

According to this discussion from 2007 in an Oracle forum, views were not cached at that time. From this description, it is clear that Oracle provides query result caching, but that will work independently from queries through a view, or selects without a view. If you use the same view in different queries, I would expect that the result caching might help a ...



Top 50 recent answers are included