Database

Re: One more query problem

4-Jun-09 21:04

How is it an error? It just means youur query returned no results. Handle it where? Please explain exactly what the problem is, my mystic powers are worn out by Friday.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

One more query problem

sujithkumarsl3-Jun-09 23:36

sujithkumarsl

3-Jun-09 23:36

Suppose i have a table like this..

COL1    COL2    COL3
X       100	  1
X       200	  2
X       300	  3
Y       100	  1
Y       200	  2
Z       300	  1

i want to select col1 where 100=1 and 200=2 and 300=3

My small attempt...

Mycroft Holmes4-Jun-09 19:42

Mycroft Holmes

4-Jun-09 19:42

In the where clause convert both col2 & 3 to strings and compare the first character of each.

Never underestimate the power of human stupidity
RAH

Re: One more query problem

Niladri_Biswas6-Jun-09 19:38

Niladri_Biswas

6-Jun-09 19:38

Solution 1:

SELECT COL1
FROM   tbl_Test
WHERE  COL2 = (COL3 * 100);

Solution 2:

SELECT COL1
FROM  tbl_Test
WHERE (COL2 = 100 AND COL3 = 1)
OR (COL2 = 200 AND COL3 = 2)
OR (COL2 = 300 AND COL3 = 3)

Solution 3:

select COL1 
from tbl_Test
where ( (COL2=100 and COL3=1) or (COL2=200 and COL3=2) or (COL2=300 and COL3=3) )

Please vote Smile | :)

Niladri Biswas

Query problem [modified]

sujithkumarsl3-Jun-09 22:31

sujithkumarsl

3-Jun-09 22:31

i am using sql server database

please check the below table two columns say column A and Column B

if the user give 100,200 and 300 i want return 1 ( which has all these values)
if the user give 100 and 200 i have to give 1 and 2. Hope this is clear.... Please help me to build the query

My small attempt...

modified on Thursday, June 4, 2009 7:50 AM

Re: Query problem [modified]

Niladri_Biswas6-Jun-09 20:01

Niladri_Biswas

6-Jun-09 20:01

Hello this time the question is not clear.

Please be more specific.

This is a very poor way of asking your question. Please define the function mapping your input to output more specifically

Tell us the desired output.

Is it

For case 1:

Input 100,200,300

Output:

A  B

1  100
1  200
1  300

For Case 2:
I/P: 100 , 200

?

Please from next time whenever you post, give the sample output so that it will be easy to solve.

Also, are you passing a comma delimited value or what?

Nothing clear!: Frown | :(

Niladri Biswas

modified on Sunday, June 7, 2009 2:39 AM

Re: Query problem

Niladri_Biswas6-Jun-09 20:44

Niladri_Biswas

6-Jun-09 20:44

Anyway

Assuming that

For case 1:

Input 100,200,300 

Output:

A  B

1  100
1  200
1  300

Solution

SELECT MIN(A), B
FROM tbl_test
WHERE B IN (100, 200, 300)
GROUP BY B;

For Case 2:

I/P: 100 , 200

O/P:

A  B
1  100
1  200
2  100
2  200

Solution is

SELECT A, B
FROM tbl_test
WHERE B IN (100, 200)
ORDER BY A, B;

hope this helps.

And vote me Smile | :)

And from next time please define the function mapping your input to output more specifically

Smile | :)

Niladri Biswas

Default Collation

hadad3-Jun-09 22:03

hadad

3-Jun-09 22:03

Hello,
When I try to insert unicode chars (Arabic chars) in my database which has its default collation to (latin) these char are inserted incorectly even if I use the nvarchar data type is this because of the collation, can II use the Arabic bin collation to resolve this proplem or ther is an other raison?

Thanks

Dad

Perfomance on the Following Query

Vimalsoft(Pty) Ltd3-Jun-09 20:54

Vimalsoft(Pty) Ltd

3-Jun-09 20:54

Good Morning All

I have Table Defined as

/****** Object:  Table [dbo].[EXP_REL_SLOT_DOMN]    Script Date: 06/04/2009 08:38:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EXP_REL_SLOT_DOMN](
	[SLOT] [int] NOT NULL,
	[DOMN] [int] NOT NULL,
	[PREF] [int] NOT NULL
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'references TBL_SLOT_ALLC.ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'EXP_REL_SLOT_DOMN', @level2type=N'COLUMN', @level2name=N'SLOT'

indexed as

/****** Object:  Index [EXP_REL_SLOT_DOMN_INDEX]    Script Date: 06/04/2009 08:41:01 ******/
CREATE UNIQUE CLUSTERED INDEX [EXP_REL_SLOT_DOMN_INDEX] ON [dbo].[EXP_REL_SLOT_DOMN] 
(
	[SLOT] ASC,
	[DOMN] ASC,
	[PREF] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF) ON [PRIMARY]

And i have Two insert statements that takes 29 seconds each

--29 seconds
INSERT into tempslot
select distinct sd1.slot as s1, sd2.slot as s2
from [dbo].[EXP_REL_SLOT_DOMN] sd1
inner join [dbo].[EXP_REL_SLOT_DOMN] sd2
on sd1.domn = sd2.domn 
and sd1.slot > sd2.slot

--29 seconds
INSERT into tempslot
select distinct sd1.slot as s1, sd2.slot as s2
from [dbo].[EXP_REL_SLOT_DOMN] sd1
inner join [dbo].[EXP_REL_SLOT_DOMN] sd2
on sd1.domn = sd2.domn 
and sd1.slot < sd2.slot

How can i improve the Perfomance of this Insert statements

Thank you

Ashfield3-Jun-09 21:04

3-Jun-09 21:04

Have you had a look at the execution plan? That should show where the most time is taken. Just in passing, why 2 inserts when 1 will do - unless I'm missing something this has the same effect

INSERT into tempslot
select distinct sd1.slot as s1, sd2.slot as s2
from [dbo].[EXP_REL_SLOT_DOMN] sd1
inner join [dbo].[EXP_REL_SLOT_DOMN] sd2
on sd1.domn = sd2.domn 
and sd1.slot != sd2.slot

It may be quicker to use a temp table, take the distinct off this query and do a distinct from the temp table as it may have many less records to process.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Vimalsoft(Pty) Ltd3-Jun-09 21:34

Vimalsoft(Pty) Ltd

3-Jun-09 21:34

Good Morning Ashfield

Thank you for your reply.

Ashfield: wrote
why 2 inserts when 1 will do - unless I'm missing something this has the same effect

If i use <> or != it will take 3:47 Seconds. by separating the Query it took less than that and there thing to note is that there is not Unique key in these table.

Thank you

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Ashfield4-Jun-09 1:31

4-Jun-09 1:31

Something I just noticed, it would probably benefit to change your index to put on domn then slot. If you think about it, you are matching on domn and looking for slot within in that domn.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

smcnulty20004-Jun-09 1:58

4-Jun-09 1:58

And consider telling us three more things;

1. Which version sql server, 2005 or 2008?
2. What is the description of tempslot?
3. What is the result when you run the select statements without the insert portion of the statement?

In other words, do they run faster by quite a bit or the same?

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.

smcnulty20003-Jun-09 22:36

3-Jun-09 22:36

Short answer: try removing the index.

Long answer:

How many records are you putting in? That's pretty critical to discussing this. 29 seconds with a billion records is different than 29 seconds with a 1000 records.

You might be better off without the index. I recommend you drop the index and retest your speeds without it.
Realize that indexes are great for lookup but there is a price to pay when you write to a table.

Every time you write to the table, the system has to rebuild the index and since you are using a clustered index you are reordering the data blocks in the same order as the index. So you write, then insert the next line, then reorder, then rewrite the whole mess. That's not exactly efficient.

And because it is a primary key the system will check the uniqueness of each line entered every one of those checks costs you.

Equally important is the fact that you are indexing all columns in one index. You really are just trading disk reads in one place for disk reads in another.

On the other side; when someone runs a select against this table what percentage of the data will be returned on a regular basis? Will it be 5% or less? Or will it be most of the table.

If you aren't going to see 5% or less then having an index is just a waste. Full table scans would be quicker.

With all of that in mind; if you must have the index then see if you can code the situation so that it drops the index, then inserts all the data in one fell swoop, then rebuilds the index.

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.

Vimalsoft(Pty) Ltd4-Jun-09 0:00

Vimalsoft(Pty) Ltd

4-Jun-09 0:00

Good Morning smcnulty2000

Thanks for your reply

<b>smcnulty2000 wrote :</b>
Short answer: try removing the index.

Without indexes it run 2 Minutes 10 seconds

Thank you for the Advice.

Ashfield4-Jun-09 1:24

4-Jun-09 1:24

smcnulty2000 wrote:
Short answer: try removing the index.

The index is on the source table, not the destination so its not going to help at all, - if it was the destination then I would agree (in principle) that it may help, but as you sya, how many records?

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

smcnulty20004-Jun-09 1:37

Find feature in report viewer not working in Reporting services

4-Jun-09 1:37

My error. I'm translating from another sql language but catching up.

At least now we know it is using the index.

Vuyiswa Maseko I'm eager to read how many records get processed.

Post the execution plan as well, if you can. That might get us some clues.

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.

catmom3-Jun-09 10:51

catmom

3-Jun-09 10:51

I have an asp.net application (VB.Net, Reporting Services 2005) which includes a report viewer that displays a client-side report. The report displays normally, but the built-in find feature is not working from the report viewer on my webpage. When the report is displayed and I enter a search string in the find textbox and click Find, the report is redisplayed from the beginning, and does not indicate any "found" text. Maybe I wasn't using the right search criteria, but I couldn't find any reference to a problem concerning this.

The Report Viewer settings from the source are:

<rsweb:ReportViewer ID="IHEEView" runat="server" Width="100%" Height="99.3%" Font-Names="Verdana" Font-Size="8pt" ShowParameterPrompts="False" ShowCredentialPrompts="False" ShowDocumentMapButton="False" BorderColor="DimGray" BorderStyle="Solid" BorderWidth="1px" DocumentMapCollapsed="True" SizeToReportContent="True" AsyncRendering="False" ShowPageNavigationControls="False">
<LocalReport ReportPath="IHEEContractor.rdlc">
</LocalReport>
</rsweb:ReportViewer>
.

I am including Microsoft.ReporViewer.Common.dll, Microsoft.ReportViewer.ProcessingObjectModel.dll, and Microsoft.ReportViewer.WebForms.dll, all Version 8.0.0.0 in the bin folder.

This is due ASAP (isn't it always!?) Smile | :)

Help is greatly appreciated.

Restore Database Parallel in sqlserver 2005

s_rajakrishna3-Jun-09 2:45

s_rajakrishna

3-Jun-09 2:45

hi everyone,

iam just doing a sample task , of restoring 4 databases Parallely as well as sequentially, through C# code. (all together size of DB's are 2GB)

sequentially restoring database one by one.
Sequetial Total Time: 1 minute 20 Seconds (approx)
1st DB: 5 second
2nd DB: 30 Second
3rd DB: 25 Second
4th DB: 20 Second ( Total 1 munute 20 Second)

Parallel restoring database all atonce. The UI of sqlserver shows Restoring for all the databases simultaneously using C# threading code.

Parallel Total Time: 1 minute 45 Seconds (approx)
1st DB: 50 th second
2nd DB: 1 minute 20 th second
3rd DB: 1 minute 45th second
4th DB: 1 minute 45th second

the total parallel time should have been 30second because that was the max time taken by large database (2nd DB - 620MB approx), but it is not so in above case.

after several iteration the total Parallel time always takes more time than total Sequential time.

the same C# code other than restoring databases in SQL SERVER like (Copying files in code, Deploying IIS through code or any other processes) saves more than half of the time.

When it comes to sql server it takes more time(the same threading code). i need to give more specific reason to my team.

i could understand answer like stress will more on sqlserver ( server resources used more) which is not good.

can Someone tell me like specific reason does sqlserver can have only one active connection at a time ( Is this true or anything like this).

IS parallel restoring possible in Sqlserver, to put it simple.

looking for an urgent reply.

Thanks
Raja

sql view

jainiraj2-Jun-09 21:34

jainiraj

2-Jun-09 21:34

hi ..
I am creating view in sql server 2000 as-

CREATE VIEW vwSam
As
(select admin.*, cust.FirstName+' '+cust.LastName as CustName
from adminmsgrecieved as admin,custdetails as cust
where admin.AdminId='111' and cust.CustId=admin.FromCustId )

From this i am getting an error invalid object adminmsgrecieved.

But when i run select query only then it works fine.

what problem may be???
and what should i do?

plz help me..
Thanx in adv

Re: sql view

smcnulty20003-Jun-09 15:34