Re: Need SQl Query - Database Discussion Boards

Member 40015965-Jan-09 2:40

5-Jan-09 2:40

hi everyone

i have create a web page where i place two DropDownList in First i bound Country List and in second one i am bounding places list according to selected country my problem is that when place list have less items it works fine but when list goes large Ex more than 5000 or more it take so much time some times it it show time out error (Sql Server). i also try with Ajax tool kit with Cascading Dropdownlist extender with web service method but problem not solve. here is my code

// for country selection

Public Shared Function getContry() As DataTable
Dim Ds As DataSet
Dim contry_list As String
contry_list = "SELECT Country_List.CountryNo,Country_List.CountryName From Country_List"
Ds = SqlHelper.ExecuteDataset(connection, CommandType.Text, contry_list) ' "Get_Country_List")

Return Ds.Tables(0)
End Function

/// for Place List

Public Shared Function getplace(ByVal CountryNo As Integer) As DataTable
Dim Ds As DataSet
Dim getplacelist As String
getplacelist = "SELECT Place_List.PlaceNo,Place_List.PlaceName From Place_List " & " WHERE Place_List.CountryNo = " & CountryNo
Ds = SqlHelper.ExecuteDataset(connection, CommandType.Text, getplacelist)
Return Ds.Tables(0)
End Function

and on .aspx page i am using

Private Sub fillCountry()
Dim dt As DataTable
dt = getContry()
ComboBox1.DataSource = dt
ComboBox1.DataValueField = dt.Columns("CountryNo").ToString
ComboBox1.DataTextField = dt.Columns("CountryName").ToString
ComboBox1.DataBind()

End Sub

Private Sub fillPlace(ByVal CountryNo As Integer)
Dim dt As DataTable
dt = getplace(CountryNo)

ListBox1.DataSource = dt
ListBox1.DataValueField = dt.Columns("PlaceNo").ToString
ListBox1.DataTextField = dt.Columns("PlaceName").ToString
ListBox1.DataBind()
ListBox1.Enabled = True
End Sub

where i am wrong plz help me

Re: Problem with large database fetching

Ashfield5-Jan-09 2:54

Ashfield

5-Jan-09 2:54

I suspect you need to look at the indices on the tables. An index on Place_List.CountryNo would certainly help. You could also look at caching as the places per country probably do not change much.

Bob
Ashfield Consultants Ltd

Re: Problem with large database fetching

Wendelius5-Jan-09 9:22

Wendelius

5-Jan-09 9:22

To add to Ashfield's post, try to also cache the countries. That would help the fillCountry sub. As Ashfield wrote, indexing and caching helps the fillPlace sub.

The need to optimize rises from a bad design.My articles[^]

Re: Problem with large database fetching

Aman Bhullar6-Jan-09 5:45

Aman Bhullar

6-Jan-09 5:45

Have you tried using Same query on Query Analyser. Is there same error or it displayes the result(Although it takes time).

Try checking the timeout of SQLCommand object in function
ExecuteDataset

By default there is no value assigned.
Try assisgning 40000, and check where the error of time out is there.

Alo check the indexing of table.

Re: Problem with large database fetching

Member 40015966-Jan-09 20:22

Member 4001596

6-Jan-09 20:22

thx for support this was error coz thr was no indexing thx a lot

Need SQl Query

karthigachawla5-Jan-09 0:48

karthigachawla

5-Jan-09 0:48

Hai,

I need a query to list a column in order.it is varchar datatype.column name is accno.I has values like AC1,AC6,AC10,BJ8,BJ20,CK12 etc..

i have to list in order like

AC1
AC2
AC10
BK1
BK2
VH1

please any one help me

Re: Need SQl Query

SeMartens5-Jan-09 1:09

SeMartens

5-Jan-09 1:09

Hi,

maybe you could work with substrings (if all of your values have two leading chars):

<br />
select * from mytable <br />
ORDER BY substr(mycolumn, 0, 2), cast(substr(mycolumn, 3) as int)<br />

Regards
Sebastian

It's not a bug, it's a feature!

Me in Softwareland.

Re: Need SQl Query

Ben Fair5-Jan-09 4:13

Ben Fair

5-Jan-09 4:13

Unless I missed something, it seems like you just need an ORDER BY clause:

select * from table order by accno

Keep It Simple Stupid! (KISS)

Re: Need SQl Query

SeMartens5-Jan-09 4:26

SeMartens

5-Jan-09 4:26

Hmm, I don't think that this is working, because a varchar-column is the order-criteria. So it will end up with this:

AC1
AC10
AC2

since it is ordering it lexicographical.

It's not a bug, it's a feature!

Me in Softwareland.

Re: Need SQl Query

Ben Fair5-Jan-09 5:51

Ben Fair

5-Jan-09 5:51

Yes, sorry, you are right; substringing will be needed.

Keep It Simple Stupid! (KISS)

Confused about instance name in SQL cluster

George_George3-Jan-09 23:15

George_George

3-Jan-09 23:15

Hello everyone,

I am learning to install SQL Cluster. I am confused about the following concepts, does anyone have any good ideas?

1. I am always confused about the differences between instance/node concept, and also confused about instance name and virtual server name for a SQL cluster during setup process. Could anyone help to clarify please?

2. Do I need a dedicated sub-network to install a SQL Cluster and connect the dedicated sub-network to the LAN (which SQL clients are working on) by hub/switch/router? Or such dedicated sub-network is not needed and all nodes in the SQL cluster could be in the same network of the clients?

thanks in advance,
George

Re: Confused about instance name in SQL cluster

Wendelius4-Jan-09 0:49

Wendelius

4-Jan-09 0:49

George_George wrote:
I am always confused about the differences between instance/node concept

Instance is the overall cluster server which is seen by the clients. It represents the SQL Server independently from how many nodes participate in the cluster. So node is the actual installation of the SQL Server. In a cluster yo would have two or more nodes.

George_George wrote:
Do I need a dedicated sub-network to install a SQL Cluster and connect the dedicated sub-network to the LAN

I think, not exactly a sub-network, but you need two independent network adapters. For example you cannot share a single network card (even if it has several ports). The idea is that you do not have a single point of failure. A heartbeat protocol is used between the nodes so it's typical to have one network card to serve the clients and one network card solely for the node conversations. This conversation between nodes can be isolated (and typically it's a good idea) from other network traffic.

The need to optimize rises from a bad design.My articles[^]

Re: Confused about instance name in SQL cluster

George_George4-Jan-09 22:23

George_George

4-Jan-09 22:23

Thanks Mika,

I have 3 more comments,

1. From your reply, I learned that differnet instances seems means different installation -- does that mean different instances will have different database enginer binary (DLL/EXE files)?

2. From the connection string itself, can we distinguish whether it is connection string for cluster and non-cluster?

3. I want to confirm with you that it is not a must for me to have two network adapter for nodes in cluster in order to deploy SQL Server cluster? i.e. could both server and client could reside on the same Class C sub-network, and each of them have only a single network adapter?

regards,
George

Re: Confused about instance name in SQL cluster

Wendelius5-Jan-09 4:33

Wendelius

5-Jan-09 4:33

George_George wrote:
differnet instances seems means different installation

Not quite, you configure an instance and you add several nodes to it. Every node must have identical installation. Instance is what the client sees, node is the machine that runs the services.

George_George wrote:
can we distinguish whether it is connection string for cluster and non-cluster

I think that a client can point to a specific node, but if you do that there's no point in clustering. Typically the client doesn't realize that there's a cluster configuration.

George_George wrote:
could both server and client could reside on the same Class C sub-network, and each of them have only a single network adapter

I think they can be on the same network, but the server must have two separate network adapters.

Perhaps these would help:
- Installing a SQL Server 2008 Failover Cluster[^]
- How to: Create a New SQL Server Failover Cluster (Setup)[^]

The need to optimize rises from a bad design.My articles[^]

Sql query

sarah_chandran3-Jan-09 3:45

sarah_chandran

3-Jan-09 3:45

If table1 has fields Studentname , departmentid
and table2 has fields departmentid,departmentname then can anyone
give me the sql query to get the department name and the number of students in each department.

Re: Sql query

Wendelius3-Jan-09 4:13

Wendelius

3-Jan-09 4:13

Sounds like homework.

Simply join and fetch the data from both tables and then group by department name. Use COUNT to calculate the amount of students.

The need to optimize rises from a bad design.My articles[^]

Re: Sql query

Vimalsoft(Pty) Ltd4-Jan-09 20:09

Vimalsoft(Pty) Ltd

4-Jan-09 20:09

Try this

SELECT Studentname , departmentid
FROM  TABLE1 T1 INNER JOIN TABLE2 T2 ON 
T1.departmentid = T2.departmentid 
ORDER BY departmentid

Hope it helps

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.tiyaneProperties.co.za
vuyiswam@its.co.za

Re: Sql query

ChandraRam4-Jan-09 22:10

ChandraRam

4-Jan-09 22:10

Vuyiswa Maseko wrote:
Try this

SELECT Studentname , departmentid
FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON
T1.departmentid = T2.departmentid
ORDER BY departmentid

The OP wants department name and count of students - your query returns neither.

Poke tongue | ;-P

Re: Sql query

sarah_chandran5-Jan-09 14:01

sarah_chandran

5-Jan-09 14:01

Thank u , just what I needed.
select deptname,COUNT(deptid) from emp,department
where emp.deptid =department.id group by deptname

how to display the data in monthwise formate in MYSQL

jenny_anu2-Jan-09 23:16

jenny_anu

2-Jan-09 23:16

hi
in my project i want to display a report to employee by month wise achivement.
if user select starting date to ending date means it has to display
month wise . if user select 1st qtr means it has to display like this.

-------------|-------------|-------------|
JAN | FEB | MAR |
-------------|-------------|-------------|
target|achive|target|achive|target|achive|
------|------|------|------|------|------|
5 | 4 | 5 | 5 | 4 | 2 |

output screen is like this.. can u guide me. pls. how to do.
Frontend is asp.net using c# and backend is MYSQL

Re: how to display the data in monthwise formate in MYSQL

Wendelius2-Jan-09 23:46

Wendelius

2-Jan-09 23:46

If you mean that months are displayed as columns, is it always a quarter or can user select for example 6 months? If the amount of months is dynamic, I think you must rotate them horizontally when displaying the data (asp.net in this case).

The need to optimize rises from a bad design.My articles[^]

Generate complex table with SQL for Crystal Report

Ryo-kun01012-Jan-09 22:08

Ryo-kun0101

2-Jan-09 22:08

Hello,
I have a problem with the exercise I am currently working with.
The database has 4 table:

PHASE
      id     Name
       1      Morning
       2      Afternoon
       3      Evening
       ...

STAFF
      id     Name
       1       John
       2       Mary
       ...

SESSION
      id      FirstdayOfWeek     DayOfWeek        Phase
       1      5 Jan 2009         Sat                1
       2      5 Jan 2009         Sun                3
       ...

DUTY
      Staff      Session
        1           1
        2           2
        ...

The program is to make report about the number of times that a staff do his/her duty in each phase in a specific month chosen by the user. It may look like this:

                   REPORT
    Staff: John (chosen by user)
    Month: Jan 2009 (chosen by user)
Date              Morning       Afternoon       Evening        ...
10 Jan 2009        yes            no               no
11 Jan 2009         no            no               no
.....
Total                3            6                0

Can you tell me how to make the SQL that give a table like the one above (with Date, Morning, Afternoon, Evening columns) to send it to the Crystal Report? Is this possible in just one SQL query? Thank you in advance.

Re: Generate complex table with SQL for Crystal Report

Wendelius2-Jan-09 22:15

Wendelius

2-Jan-09 22:15

I think you should have a look at DATEPART[^] and CASE[^] combined with joins.

The need to optimize rises from a bad design.My articles[^]

Re: Generate complex table with SQL for Crystal Report

Ryo-kun01013-Jan-09 11:34

Ryo-kun0101

3-Jan-09 11:34

Hi Mika,
thank you for your answer. Of course I have already tried datepart, case and join and it works well with the current example. My problem is the '...' in my writing above. That means the database can be change and unfortunately, my report depends on the Phase table. The number of column, the name of each column, all are dynamic. Can you tell me how to do this in just one SQL query? If it is impossible, is there any solution like adding some more tables or writing some more code?

Re: Generate complex table with SQL for Crystal Report

Wendelius3-Jan-09 22:13

Wendelius

3-Jan-09 22:13

Have you tested PIVOT. If you already have and still not getting the desired result, I think you should use dynamic SQL either by creating a procedure or from the client. If I understood you correctly, first fetch all the phases, after that construct a SQL query with amount of columns based on phase table. After that, use that statement to return the data.

The need to optimize rises from a bad design.My articles[^]

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