3G ram for SQL server: challenging

Showing posts with label challenging. Show all posts

Thursday, March 22, 2012

A Challenging Question

How does one figure out, I mean compare the results after some changes
have been done to the SQL Server, I mean the method in which the
comparision is done, lets say I have a query I ran it it gave me the
results in 40 seconds , I ran it again this time it took 30 seconds,
then I again ran it it took 35 seconds, I then created an index , this
time the query ran in 30 seconds ... how does one compare such things ,
I mean i need to give stats as too what kind of performace has takern
place ... please helpo , I need to knw as a DBa how would you convince
your maanger that becasue of some changes the performance has improved,
cause when you ask the users they say its ok , we dont see the
differnece & stuff , please help.This is a multi-part message in MIME format.
--060509040907050200030402
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
If you don't see any difference in response times then the change has
probably not improved the performance at all.
If I was trying to ascertain whether a new index made a difference or
not I would look at the query execution plan before & after the index
was created to see if the new index is now getting used or not. I would
also run the query with "statistics io" and "statistics time" on before
& after the index was created to see what real difference it makes to IO
and elapsed time.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Double_B wrote:
>How does one figure out, I mean compare the results after some changes
>have been done to the SQL Server, I mean the method in which the
>comparision is done, lets say I have a query I ran it it gave me the
>results in 40 seconds , I ran it again this time it took 30 seconds,
>then I again ran it it took 35 seconds, I then created an index , this
>time the query ran in 30 seconds ... how does one compare such things ,
>I mean i need to give stats as too what kind of performace has takern
>place ... please helpo , I need to knw as a DBa how would you convince
>your maanger that becasue of some changes the performance has improved,
>cause when you ask the users they say its ok , we dont see the
>differnece & stuff , please help.
>
>
--060509040907050200030402
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>If you don't see any difference in response times then the change
has probably not improved the performance at all. 
 
If I was trying to ascertain whether a new index made a difference or
not I would look at the query execution plan before & after the
index was created to see if the new index is now getting used or not.
I would also run the query with "statistics io" and "statistics time"
on before & after the index was created to see what real difference
it makes to IO and elapsed time. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Double_B wrote:
<blockquote
cite="mid1140406834.987103.198470@.o13g2000cwo.googlegroups.com"
type="cite">
<pre wrap="">How does one figure out, I mean compare the results after some changes
have been done to the SQL Server, I mean the method in which the
comparision is done, lets say I have a query I ran it it gave me the
results in 40 seconds , I ran it again this time it took 30 seconds,
then I again ran it it took 35 seconds, I then created an index , this
time the query ran in 30 seconds ... how does one compare such things ,
I mean i need to give stats as too what kind of performace has takern
place ... please helpo , I need to knw as a DBa how would you convince
your maanger that becasue of some changes the performance has improved,
cause when you ask the users they say its ok , we dont see the
differnece & stuff , please help.
</pre>
</blockquote>
</body>
</html>
--060509040907050200030402--|||But then at times seeing the time the query gets executed also keeps
differing, sometimes a query takes 10 sec , the same may take 12 or 15
secs the next times its executed, ...het does anyone have a document
that really explain you the execution plan & how to read the estimated
time the plan takes & figure out whats the issue & how to tackle it
Thanks,|||This is a multi-part message in MIME format.
--070908070206060606050806
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
If you post the execution plan (& the schema for the tables involved) we
may be able to help you. Response times tend to vary a little due to
unrelated (or at least not directly related) things such as query plan
compilation/recompilation, I/O waits (busy disks), lock & latch waits
(waiting for another connection to release the locks on the indexes you
want to access), other apps hogging CPU cycles, etc.
As for documentation on execution plans, Kalen Delaney wrote an
excellent book about 5 years ago called Inside SQL Server 2000
<http://www.amazon.com/gp/product/0735609985/sr=8-1/qid=1140760885/ref=pd_bbs_1/104-5347882-5925515?%5Fencoding=UTF8>.
It has a couple good chapters in it on the query processor (Ch15) and
query tuning (Ch16) that may clarify things for you. If not then it's
still a very worthwhile volume to have on your professional bookshelf if
you're serious about SQL Server.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Double_B wrote:
>But then at times seeing the time the query gets executed also keeps
>differing, sometimes a query takes 10 sec , the same may take 12 or 15
>secs the next times its executed, ...het does anyone have a document
>that really explain you the execution plan & how to read the estimated
>time the plan takes & figure out whats the issue & how to tackle it
>Thanks,
>
>
--070908070206060606050806
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>If you post the execution plan (& the schema for the tables
involved) we may be able to help you. Response times tend to vary a
little due to unrelated (or at least not directly related) things such
as query plan compilation/recompilation, I/O waits (busy disks), lock
& latch waits (waiting for another connection to release the locks
on the indexes you want to access), other apps hogging CPU cycles, etc. 
 
As for documentation on execution plans, Kalen Delaney wrote an
excellent book about 5 years ago called <a
href="http://links.10026.com/?link=Inside">http://www.amazon.com/gp/product/0735609985/sr=8-1/qid=1140760885/ref=pd_bbs_1/104-5347882-5925515?%5Fencoding=UTF8">Inside
SQL Server 2000</a>. It has a couple good chapters in it on the query
processor (Ch15) and query tuning (Ch16) that may clarify things for
you. If not then it's still a very worthwhile volume to have on your
professional bookshelf if you're serious about SQL Server.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Double_B wrote:
<blockquote
cite="mid1140752342.020403.82750@.p10g2000cwp.googlegroups.com"
type="cite">
<pre wrap="">But then at times seeing the time the query gets executed also keeps
differing, sometimes a query takes 10 sec , the same may take 12 or 15
secs the next times its executed, ...het does anyone have a document
that really explain you the execution plan & how to read the estimated
time the plan takes & figure out whats the issue & how to tackle it
Thanks,
</pre>
</blockquote>
</body>
</html>
--070908070206060606050806--|||Thanks a lot

A Challenging Question

A challenging query about grouping and order

Greetings !!

I am facing a challenge and hope some one can help me with the query.

I have a school. School have classrooms. Classrooms have students. Classrooms are divided into various sections (Section A, Section B and so on) .Every student is allocated a rollnumber in that section. When a student is transfered from one section to the other, there are gaps in the roll number of other students of the same section. For eg:

Section A

Student 1 Roll no. 1
Student 2 Roll No. 2
Student 3 Roll No 3

Student 4 Roll no 4 and so on.

Now lets say student 2 was transferred to section B, so now there is a gap as roll no 2 is missing in section A

So what should happen is that roll no need to be grouped together based on the section and the rest be readjusted accordingly. So when a student is removed, the roll nos of other students need to be adjusted accordingly. In our case, student 3 will get a roll no of 2, student 4 will have roll no of 3 and so on.

How do I write a query for this. I need to run this as a batch process every fortnight.

Thanks

Suppose if the Roll No is not a foriegn key for other tables then I will agree with your design.

Solution for SQL Server 2005,

Code Snippet

Create Table #data (

[RId] int ,

[Class] int ,

[Section] Char ,

[RollNo] int

);

Insert Into #data Values('1','1','A','1');

Insert Into #data Values('2','1','A','2');

Insert Into #data Values('3','1','A','3');

Insert Into #data Values('4','1','A','4');

Insert Into #data Values('5','1','A','5');

Insert Into #data Values('6','1','A','6');

Insert Into #data Values('7','1','B','1');

Insert Into #data Values('8','1','B','2');

Insert Into #data Values('9','1','B','3');

Insert Into #data Values('10','1','B','4');

Insert Into #data Values('11','1','B','5');

Insert Into #data Values('12','1','B','6');

Insert Into #data Values('13','1','B','7');

Insert Into #data Values('14','1','B','8');

Insert Into #data Values('15','1','B','9');

--Transfer the Student 4 from A section to B section

Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4

--To fill the current gap

;WITH CTE

(

Select * , Row_Number() OVER (Partition By Class,Section Order By RollNo) NewRollNo

from #data

)

Update

CTE

Set

RollNo = NewRollNo

Where

RollNo <> NewRollNo

|||

For SQL Server 2000,

Code Snippet

Create Table #data (

[RId] int ,

[Class] int ,

[Section] Char ,

[RollNo] int

);

Insert Into #data Values('1','1','A','1');

Insert Into #data Values('2','1','A','2');

Insert Into #data Values('3','1','A','3');

Insert Into #data Values('4','1','A','4');

Insert Into #data Values('5','1','A','5');

Insert Into #data Values('6','1','A','6');

Insert Into #data Values('7','1','B','1');

Insert Into #data Values('8','1','B','2');

Insert Into #data Values('9','1','B','3');

Insert Into #data Values('10','1','B','4');

Insert Into #data Values('11','1','B','5');

Insert Into #data Values('12','1','B','6');

Insert Into #data Values('13','1','B','7');

Insert Into #data Values('14','1','B','8');

Insert Into #data Values('15','1','B','9');

--Transfer the Student 4 from A section to B section

Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4

--To fill the current gap

Update #data

Set

RollNo = (Select Count(*) From #data Sub

Where Sub.Class=#data.Class And Sub.Section=#data.Section And Sub.RollNo<= #data.RollNo)

|||

Thanks Manivannan for the solution. I will try the solution and revert back.

Regards,

Lalit

|||

Hi,

Currently the query is for a single classroom. Could you also suggest me how to extend this query to run for multiple classrooms one after the other.

I hope you help me out. Thanks a ton Manivannan.

|||The above query is capable to handle multiple calssrooms also..

A challenging query about grouping and order

Greetings !!

I am facing a challenge and hope some one can help me with the query.

Section A

Student 1 Roll no. 1
Student 2 Roll No. 2
Student 3 Roll No 3

Student 4 Roll no 4 and so on.

Now lets say student 2 was transferred to section B, so now there is a gap as roll no 2 is missing in section A

How do I write a query for this. I need to run this as a batch process every fortnight.

Thanks

Suppose if the Roll No is not a foriegn key for other tables then I will agree with your design.

Solution for SQL Server 2005,

Code Snippet

Create Table #data (

[RId] int ,

[Class] int ,

[Section] Char ,

[RollNo] int

);

Insert Into #data Values('1','1','A','1');

Insert Into #data Values('2','1','A','2');

Insert Into #data Values('3','1','A','3');

Insert Into #data Values('4','1','A','4');

Insert Into #data Values('5','1','A','5');

Insert Into #data Values('6','1','A','6');

Insert Into #data Values('7','1','B','1');

Insert Into #data Values('8','1','B','2');

Insert Into #data Values('9','1','B','3');

Insert Into #data Values('10','1','B','4');

Insert Into #data Values('11','1','B','5');

Insert Into #data Values('12','1','B','6');

Insert Into #data Values('13','1','B','7');

Insert Into #data Values('14','1','B','8');

Insert Into #data Values('15','1','B','9');

--Transfer the Student 4 from A section to B section

Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4

--To fill the current gap

;WITH CTE

(

Select * , Row_Number() OVER (Partition By Class,Section Order By RollNo) NewRollNo

from #data

)

Update

CTE

Set

RollNo = NewRollNo

Where

RollNo <> NewRollNo

|||

For SQL Server 2000,

Code Snippet

Create Table #data (

[RId] int ,

[Class] int ,

[Section] Char ,

[RollNo] int

);

Insert Into #data Values('1','1','A','1');

Insert Into #data Values('2','1','A','2');

Insert Into #data Values('3','1','A','3');

Insert Into #data Values('4','1','A','4');

Insert Into #data Values('5','1','A','5');

Insert Into #data Values('6','1','A','6');

Insert Into #data Values('7','1','B','1');

Insert Into #data Values('8','1','B','2');

Insert Into #data Values('9','1','B','3');

Insert Into #data Values('10','1','B','4');

Insert Into #data Values('11','1','B','5');

Insert Into #data Values('12','1','B','6');

Insert Into #data Values('13','1','B','7');

Insert Into #data Values('14','1','B','8');

Insert Into #data Values('15','1','B','9');

--Transfer the Student 4 from A section to B section

Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4

--To fill the current gap

Update #data

Set

RollNo = (Select Count(*) From #data Sub

Where Sub.Class=#data.Class And Sub.Section=#data.Section And Sub.RollNo<= #data.RollNo)

|||

Thanks Manivannan for the solution. I will try the solution and revert back.

Regards,

Lalit

|||

Hi,

Currently the query is for a single classroom. Could you also suggest me how to extend this query to run for multiple classrooms one after the other.

I hope you help me out. Thanks a ton Manivannan.

|||The above query is capable to handle multiple calssrooms also..

A Challenging Query

Hi EveryBody,
Here is a very interesting SQL which I failed to solve.
I have two tables called Main and Notes.Table notes has id field which is
primary key of the Main table and foreign key in notes table. Notes table has
an identity column called NotesId.
What I have to do is that I have to show all data of main table but they
will be ordered depending upon a particular value of the note field in the
notes table. What I mean is : say there is ID 100,101,102,103 existing in the
main table. They may have several entries in the notes table and some of
those entries containing say "Desired match" in their note field.
What I want : in time of selection those entries who have "Desired Match" in
their notes field they will be coming first in their chronological order.
But their are some constraints : you cannot use any group by or distinct
clause in the query. and the resultant data cannot have any duplicate rows
Here is what I tried :
select top 100 percent main.*,notes.note,notes.date
from main left join notes on main.id=notes.id
order by convert(
numeric,
case
when notes.note like('Desired%') then '100000'
else
'500'
end
) desc,notes.date asc
But problem is that this result set contains duplicate data
Any new or better Idea.
Thanks
Kaushik
On Wed, 23 Mar 2005 22:23:01 -0800, Kaushik wrote:

>Hi EveryBody,
>Here is a very interesting SQL which I failed to solve.
(snip)
Hi Kaushik,
I love solving SQL challenges. But I'm not quite as good at trying to
understand verbose descriptions. I'll refer you to a website that
explains what information you should include in a post in order for us
to help you: www.aspfaq.com/5006. If you follow the guidelines given
there, I'll probably be able to help you.

>But their are some constraints : you cannot use any group by or distinct
>clause in the query. and the resultant data cannot have any duplicate rows
I understand the need to eliminate duplicate rows, but the constraints
to not use GROUP BY or DISTINCT makes no sense to me. Can you explain
the reason for this restriction? Because I really don't understand why
any SQL coder would voluntarily part with part of the tools he needs to
do his job.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Tuesday, March 20, 2012

A challenging CUBE related question

Hi,

I have data coming in from our OLTP's view which has the following sales related information:

1) Date of Sale
2) Product Sold
3) Customer to which the Product was sold
4) QTy. sold
5) Total Sale Amount

I am bringing in this information into my "staging area" and from there I am building my Customer, Product, and Time Dimension and obviously my FACT table. The measures in my fact table were TotalSalesand Total QtySold and everything was working fine and I was able to analyze, slice, dice my cube in many ways...

Now the problem:

I also have planning related data available from another OLTP based view which contains the following information:

1) Plan Year
2) Plan Month (using Plan Year and Plan month,I "derived" a date which for any month was the 1st of that month)
3) Planned Product
4) Planned Sale (Amount)
5) Planned Qty (for sale)

You would have noticed that planning is NOT done at "Customer Level" and the planning data is only for product and time level (and that too only upto month level).

After populating my FAct table from the Sales data, I appended the planning related data into my fact table hoping that as long as I do not select any thing in the Customer dimesion and only go as low as the month level on my Time dimension, I will be able to see all of the following (measures) for any/all products:

Planned Sale, Actual Sale, Planned Qty., Actual Qty,

However I am unable to get anything displayed in the Planned Sale and Planned Qty despite applying all of the little knowledge that Ihave so far in the world of BI and making data marts
...

I really hope that my questions and requirement is clear.Can someone please help me get to

the solution.

Many many TIA.I hope I understand you correctly. If I did, I would have thought of doing the following (thinking from OLAP standpoint):

Dimensions:
- Create shared dimension for Time to support Date of Sale and Plan Month, Year
- Create shared dimension for Product

Cubes/facts:
- Create two separate cubes one for Actual and one for Planned using the shared dimensions. The Actual can have the Customer as a Private Dimension (it may help to design the customer also as a shared dimension).
- Create a virtual cube on top of the Actual and Planned.
- Create calculated measures for differentials between Actual and Planned.

Murthy

Thursday, March 22, 2012

A Challenging Question

A Challenging Question

A Challenging Question

A challenging query about grouping and order

A challenging query about grouping and order

A Challenging Query

Tuesday, March 20, 2012

A challenging CUBE related question

3G ram for SQL server

Blog Archive

About Me