Click here to Skip to main content

Database

Re: subquery using 1 table

john john mackey17-Oct-12 4:38

john john mackey

17-Oct-12 4:38

Thanks for all the replies and suggestions. as it stands the table structure does reveal different AccountNames when queried on different columns (AccountID, CustomerID). And yes, that seems redundant because for case of one record having unique set of AccountID, CustomerID, and AccountName it is obvious.
For case of many records with mixed combinations of those 3 columns, I am not guaranteed the AccountName is what I want.
I think I'm only concerned if AccountName is not NULL.
Thanks!

High Number of Lock Requests

cjb11016-Oct-12 3:23

16-Oct-12 3:23

We're seeing a large number of lock requests / sec on our SQL 2008 db, the current average is ~240,000.

They seem to be either key locks or page locks. There's no time-outs, deadlocks and the average wait time is 0.
The only other stat that seems high is the context switches, which is around 1k-3k on our 2cpu system.

Is this something to be worried about?

Re: High Number of Lock Requests

Eddy Vluggen17-Oct-12 0:17

17-Oct-12 0:17

cjb110 wrote:
Is this something to be worried about?

Only if you have time to spare. It might hint that additional optimization is possible, but it's not guaranteed that it can be optimized further - it'd be a fun exercise though. You might find this[^] an interesting read to get you started.

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

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

SQL Select Where query

Joe Stansfield15-Oct-12 0:37

15-Oct-12 0:37

Hi all,

Firstly, I must confess to being a very amateur programmer with a lot to learn! However, I'm battling away and am slowly getting my head around things.

I have a query I have written which as follows:

SELECT File_Name
WHERE (File_Name = @File_Name1)

File_Name1 is a user input parameter. My problem is that, even if the record does not exist, it returns the record, resulting in an error on the client side.
Is it possible to edit that query to return a file_name field value of say, 1, if the searched record doesn't exist?

Any help appreciated! (Also for note most of my queries are generated by VStudio, so my understanding of them leaves a lot to be desired...)

Re: SQL Select Where query

Eddy Vluggen15-Oct-12 0:57

15-Oct-12 0:57

Using which database-dialect? If you're using Sql Server, then the IsNull[^]-function might be handy.

SQL

SELECT ISNULL(File_Name, '1')
  FROM Files
 WHERE File_Name = @FileName1

If we're talking about more than a single record, you'd need a join and a dummy-table.

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

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

Re: SQL Select Where query

Joe Stansfield15-Oct-12 1:15

15-Oct-12 1:15

Yea I am using SQL Server.

At the moment the query is designed to open file which the user requests (file_name). This works really well if the input matches a record in the database.

The problem is when it doesn't match a stored record. Will using the query as above with ISNULL work? And if so, am I right in assuming that the query first executes the where part of the query, then the IFNULL portion and, if still valid after that, retrieves the correct record?

Re: SQL Select Where query

Eddy Vluggen15-Oct-12 1:22

15-Oct-12 1:22

Joe Stansfield wrote:
The problem is when it doesn't match a stored record. Will using the query as above with ISNULL work?

Dunno - it will not return an empty record, but it'll return '1' when the value is empty. Would that work with the rest of your code?

Joe Stansfield wrote:
And if so, am I right in assuming that the query first executes the where part of the query, then the IFNULL portion and, if still valid after that, retrieves the correct record?

Yes.

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

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

Re: SQL Select Where query

Joe Stansfield15-Oct-12 1:28

15-Oct-12 1:28

Yea that will fix up the error from no value being entered.

But I'm struggling to understand and make it work if, say, an input of 100 is entered. If 100 isn't a valid ID of a record, it won't return anything. I'd like it to return 1 for ease of avoiding an error and possible loop error (which could also be put down to poor programming by me on the C# side of things).

With that said, if 99 is entered and that is a valid record, then 99 would need to be returned by the query.

Sorry for not understanding your explanation.

Re: SQL Select Where query

Eddy Vluggen15-Oct-12 2:15

15-Oct-12 2:15

Joe Stansfield wrote:
With that said, if 99 is entered and that is a valid record, then 99 would need to be returned by the query.

That's not how things are done usually; all records in the database "should" be valid records with a link. If not, you got a modeling-error that could bite you later on.

We're using "empty" values (nulls) if we can not link to another record. The database-server is built around that concept; you can get all records from two (or more tables) "joined" into a single table, explicitly asking for a collection with/without the linked records.

Joe Stansfield wrote:
I'd like it to return 1 for ease of avoiding an error and possible loop error

What loop? Do you have an example?

FWIW, you don't need to loop to load all records; you fetch 'em in a single haul.

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

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

Re: SQL Select Where query

Joe Stansfield15-Oct-12 10:48

15-Oct-12 10:48

Yea, from reading what you have said, I think my error may be in allowing a user to input a value into the query that is not a valid record (ie one that doesn't exist).

So would the best approach be to run a query to test if input value is a record, then if true, proceed to the query which returns the record?

The loop error is because of my attempting to sort a datagrid with linked data in it - if a null value is returned (ie no record), it gets caught in a loop and error because it can't sort a null record (along those lines).

Thanks for all your help, even though I haven't fixed the problem I am picking up some valuable tips!

Re: SQL Select Where query

fjdiewornncalwe15-Oct-12 11:06

fjdiewornncalwe

15-Oct-12 11:06

You shouldn't need to run 2 queries. You should just need to check the resulting dataset to see if you get any results. (ie. rowcount > 0)

I wasn't, now I am, then I won't be anymore.

Re: SQL Select Where query

Joe Stansfield16-Oct-12 0:08

16-Oct-12 0:08

Thanks for all the help guys.

After reading what you have all posted and doing some more research I managed to solve the problem I was having.

The basic query I was running was sufficient, it turned out the problem was in an automatically created linked query. By fixing that I have got ride of the errors I was getting.

Re: SQL Select Where query

Eddy Vluggen16-Oct-12 0:12

16-Oct-12 0:12

Joe Stansfield wrote:
So would the best approach be to run a query to test if input value is a record, then if true, proceed to the query which returns the record?

The best approach is one without loops. Are you displaying the "linked record" in a separate (child) gridview?

Usually, we combine the tables in Sql, like this;

SQL

SELECT a.Field1
      ,a.Field2
      ,b.Field1 AS Field3
  FROM Employee AS a
  JOIN Department AS b ON a.DepartmentId = b.Id

This will generate a single resultset, and depending on the type of join, the database will fetch the employees with, those without a linked record, or both Smile | :)

Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

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

Re: SQL Select Where query

Joe Stansfield16-Oct-12 23:04

16-Oct-12 23:04

Yea I have a linked record showing in a child view.

So from what I can gather the first query executes to fill the main record table, and then a second query populates the correct record in the grid view (and a few other text boxes on the form).

It is working now - but is it worth the time to write it as it should be? It won't affect any of my datasource bindings by combing it into a single query will it?

Re: SQL Select Where query

Eddy Vluggen16-Oct-12 23:37

16-Oct-12 23:37

Joe Stansfield wrote:
It is working now - but is it worth the time to write it as it should be?

It's already as it should be; if you have a separate grid for the children, you'll need a second query. Also explains why the second grid might get an empty resultset.

Joe Stansfield wrote:
It won't affect any of my datasource bindings by combing it into a single query will it?

It would! The net effect is that you only pull the data over the network-line once. You're using a different concept here - if you'd fetch all tables, you'd probably have fetched a lot of child-records that the user isn't going to use. That means that your current approach might be the more efficient one, depending on what the user does Smile | :)

Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

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

Re: SQL Select Where query

Member 952116716-Oct-12 22:41

16-Oct-12 22:41

ITS BETTER U YO USE THE IMMEDIATE CONSTRAINT DURING THE CODING

Re: SQL Select Where query

Simon_Whale16-Oct-12 22:52

16-Oct-12 22:52

Please don't shout (capital letters are considered shouting!).

I would also refrain from using text speak as it upsets a lot of people on these forums.

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Re: SQL Select Where query

Rohit Shrivastava18-Oct-12 7:56

Rohit Shrivastava

18-Oct-12 7:56

Hi Joe,

You can use following query:

SQL

SELECT ISNULL( (SELECT File_Name
  FROM Files
 WHERE File_Name = @FileName1),1)

This would solve your problem, in case there is no result, it will return you 1 otherwise the matching result.

Re: SQL Select Where query

Joe Stansfield15-Oct-12 1:20

15-Oct-12 1:20

There are also a number of other columns for each row, I assume that I just populate the query with those separated by columns?

Re: SQL Select Where query

Eddy Vluggen15-Oct-12 1:25

15-Oct-12 1:25

Yup

Smile | :)

SQL

SELECT Col1
      ,ISNULL(Col2, '1')
      ,Col3
      ,Col4
  FROM SomeTable
 WHERE SomeCondition

..or even nest another select, like this;

SQL

SELECT Col1
      ,ISNULL(Col2, '1')
      ,(SELECT 1) AS Col3
      ,Col4
  FROM SomeTable
 WHERE SomeCondition

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

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

Re: SQL Select Where query

Nidhisha Biju15-Oct-12 1:05

15-Oct-12 1:05

SQL

SELECT isnull(File_Name,1) as FileName from tblname
WHERE (File_Name = @File_Name1)

Re: SQL Select Where query

Eddy Vluggen15-Oct-12 1:26

15-Oct-12 1:26

Darn, you're right Big Grin | :-D

Big Grin | :-D

I was returning the value as a VARCHAR, not an INTEGER - well spotted.

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

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

Re: SQL Select Where query

Aadhar Joshi16-Oct-12 20:47

16-Oct-12 20:47

Hi,
U can use following statment..

SQL

SELECT ISNULL(File_Name, '')
  FROM Files
 WHERE File_Name = @FileName1

because is is string so it should return blank

Re: SQL Select Where query

Hitesh R23-Oct-12 1:06

23-Oct-12 1:06

Try the following query.

SQL

SELECT CASE WHEN COUNT(File_Name)=0 THEN 1 ELSE COUNT(File_Name) END as FileName from tblname
WHERE (File_Name = @File_Name1)

MS SQL didn't allow to store apostrophe( ' ) in name

Azher.Cybertech15-Oct-12 0:04

Azher.Cybertech

15-Oct-12 0:04

if v want to store name like Mohammed's then sql didn't allow to save data and give an error

General News Suggestion Question Bug Answer Joke Praise Rant Admin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.