Database

Re: whats diffrence between in two query in performance respect

2-Aug-13 5:48

hi to all
whats diffrence between two below query in performance respect:
query 1:

SQL

SELECT  n, dbo.AddOne(n) AS r
FROM dbo.T1
ORDER BY r;

function addOne is this:

SQL

CREATE FUNCTION dbo.AddOneInline(@n AS BIGINT) RETURNS TABLE
AS
RETURN SELECT @n + 1 AS val;
GO

and query2:

SQL

SELECT  n, (SELECT val FROM dbo.AddOneInline(n)) AS r
FROM dbo.T1
ORDER BY r;

and defination of function is:

SQL

GO
CREATE FUNCTION dbo.AddOneInline(@n AS BIGINT) RETURNS TABLE
AS
RETURN SELECT @n + 1 AS val;
GO

query 2 is very good in performance and i want to know whats difference between in this two query

Eddy Vluggen2-Aug-13 8:32

Database for Bridge Asset Management

2-Aug-13 8:32

The difference is that one is written out as a subquery.

It's a micro-optimization IMHO, but you can use the Management Studio[^] to check the execution plan and compare both versions.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Nebilo1-Aug-13 22:25

Nebilo

1-Aug-13 22:25

Hi everyone,

I wanted to ask a question that was confusing me how to design the db.This bridge database that i was going to design has 3 different districts(Locations) say A,B and C.These districts have different road segments,sections.I temporarily designed the db using separate tables but having the same fields.As i said these tables only differ by the thing i mentioned.

I know I'm not following the design rules,so i need you to help me on that.

Thanks

Bernhard Hiller2-Aug-13 0:10

Bernhard Hiller

2-Aug-13 0:10

And when the next district is added, you add another table?
No! Create a table for the districts, and then use "foreign keys" to connect to the sections table.

Mycroft Holmes2-Aug-13 1:07

Mycroft Holmes

2-Aug-13 1:07

Bernhard Hiller wrote:
foreign keys

What be these foreign keys you speak of!

Never underestimate the power of human stupidity
RAH

Nebilo2-Aug-13 1:47

Nebilo

2-Aug-13 1:47

BTW there are fields that provide the bridge information,it's bridge number(p.k),name,design year,asset value and other fields.
Now there are 3 different districts A,B and C.Under District A,there are different sections and road segments that correspond to A only.Same thing goes to B and C.These districts are not related in any way in other words.
That's why i created 3 separate tables based on these districts.

Eddy Vluggen2-Aug-13 0:29

face time out when run query with nested select

2-Aug-13 0:29

Nebilo wrote:
I know I'm not following the design rules,so i need you to help me on that.

How? Yes, I could sum up some widely used constructs, but that's not a decent alternative for a good design.

There's explanation on database-normalization in the wikipedia, and various articles on CodeProject. Alternatively, you could fake a design[^].

I'll gladly help with the normalization rules if you get stuck Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

mhd.sbt1-Aug-13 21:54

Re: face time out when run query with nested select

1-Aug-13 21:54

hi to all
when i run this query i faced with timeout

SQL

DELETE FROM bml.LoanInstallment WHERE ID IN (select * from wfInstanceNodes where ID=@nodeID)

this occurred when nested select return null and i think it is because of that i have no index on id;
and i set index to id but still this query faced with time out

note that bml.LoanInstallment has 11000000 record and this query run without problem for a table with
a few records.
thanks for any help

Bernhard Hiller2-Aug-13 0:09

Bernhard Hiller

2-Aug-13 0:09

Do you run into a timeout when you do a SELECT instead of a DELETE? E.g. SELECT COUNT(ID) FROM bml... etc.? And how many entries are to be deleted?
Are there indices for the ID columns in both tables?
Are there any constraints, e.g. an OnDeleteCascade?

Re: face time out when run query with nested select

mhd.sbt2-Aug-13 2:45

Re: face time out when run query with nested select

2-Aug-13 2:45

id is uniqueness and
and one entries finally will be deleted.
and i don't have any constraint on bml table
thanks in advanced

Eddy Vluggen2-Aug-13 0:10

Re: face time out when run query with nested select

2-Aug-13 0:10

Your subquery should not select "all" columns, only the ID-column.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

mhd.sbt2-Aug-13 2:42

Re: face time out when run query with nested select

2-Aug-13 2:42

ok i use select id instead of select * but im still face with timeout
i think that this is because of that my nested select return an empty column
what you thinks ?
how can i handle this problem?
thanks for answer

Eddy Vluggen2-Aug-13 8:27

Re: face time out when run query with nested select

2-Aug-13 8:27

mhd.sbt wrote:
i use select id instead of select * but im still face with timeout

A command-timeout or a connection-timeout? How long does the query take when run on the server directly?

Can you post the schema of the tables here? It would be weird if you'd select the same key that you're filtering on.

mhd.sbt wrote:
what you thinks ?

I'm pretty sure that you can select "where in null" without hitting performance-problems of said magnitude.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

mhd.sbt4-Aug-13 8:59

Re: face time out when run query with nested select

4-Aug-13 8:59

thanks for your time to answer this problem.
i found problem
that i posted it in a solution

PIEBALDconsult2-Aug-13 7:40

PIEBALDconsult

2-Aug-13 7:40

Personally, I don't like sub-selects (they seem so 90s), I'd rather JOIN them.

(Provided your system allows it)

-- Transact-SQL extension
USE AdventureWorks2012;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;

Problem at Sql Server Configuration manager

bikah chanda mohanta30-Jul-13 23:42

bikah chanda mohanta

30-Jul-13 23:42

I have installed SQLServer2008R2 on my Windows 7
PC successfully.
However, when I open the configuration manager,
the "SQL Server Services" entry shows an error message saying
"The remote procedure call failed 800706be".

Re: Problem at Sql Server Configuration manager

Simon_Whale30-Jul-13 23:46

Simon_Whale

30-Jul-13 23:46

I'd have a read of this as a starting point.

Remote Procedure call failed in SQL Server Configuration Manager[^]

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

database mirroring / database replication.

Code-Hunt29-Jul-13 23:34

Code-Hunt

29-Jul-13 23:34

Hi all

In c# forum i asked the below question

Hi all
 
Please clear my doubt.
I have one server system where i installed sql server.
5 client machines accessing that.
now suppose if there is any power fluctuation like it's coming for 5 min and again going and so on.
So for this kind of situation, how to save the data or how to update the database each second.??
Is it possible that at a time we update the client database also and server database also.?
i.e at button click it should update both client and server database..
 
Please help..

Then i got the suggestion of database mirroring / database replication.

I am very much new to this.

Please need suggestion from you guys.

jschell30-Jul-13 8:31

jschell

30-Jul-13 8:31

Code-Hunt wrote:
Please need suggestion from you guys.

Google the topic. And buy a book or perhaps two.

Then set up some test servers. And test it. After you figure out the pro/cons of the possible different ways to do this.

Naturally of course would also need to consider other types of failures, which means that you need the two server located in different geographic areas. That of course means you must not only consider the methodology of replication but the cost. And find hosting companies to host at least on of the servers.

You must also test it and insure that the servers continue to run in production.

With the cost you might also consider the possibility that doing all of this yourself might not only cost a lot (if you are being paid) but will also have a high risk. Consequently finding a hosting service that provides all of the above but be more effective.

Jörgen Andersson30-Jul-13 11:40

Jörgen Andersson

30-Jul-13 11:40

Get a UPS[^] for all affected systems including switches.

Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln

Mycroft Holmes30-Jul-13 12:30

Mycroft Holmes

30-Jul-13 12:30

And what sort of answer is that, it has nothing to do with databases, he did not ask for a solution to his problem he wanted to know about mirroring D'Oh! | :doh:

I feel like an idiot for not thinking of that!

Never underestimate the power of human stupidity
RAH

Jörgen Andersson31-Jul-13 10:04

Jörgen Andersson

31-Jul-13 10:04

I believe someone didn't get the sarcasm, (over)compensating.

Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln

Eddy Vluggen31-Jul-13 9:05