如何在多个条件下优化sql查询?

table station_tbl:

表station_tbl:

station_name   |  fare_adult |  fare_child |  fare_OKU |  type |    stationid

New City            2.00         1.00         1.00       0            19900
Old Village         2.00         1.00         1.00       0            54900
Old City            5.00         2.50         2.50       1            23100
New Castle          1.00         0.50         0.50       1            22900
Adult                                                    2              0
Child                                                    2              1
OKU                                                      2              2
Single                                                   3              0
Return                                                   3              1

my query: display ticket info

我的查询:显示票信息

SELECT
    A.ticketid,
    B.station_name AS stationid,
    c.station_name AS destination,
    CONVERT(VARCHAR(5), GETDATE(), 108) AS TIME,
    CONVERT(VARCHAR(12), GETDATE(), 103) AS Date,
    D.station_name AS ticket_type,
    e.station_name AS journey_type,
    amount,
    issuedby
FROM ticketcollections AS A, 
    station_tbl AS B, 
    station_tbl AS c, 
    station_tbl AS D, 
    station_tbl AS e
WHERE A.ticketidparent = '" + Request("ParentId") + "'
    AND A.stationid = B.stationid
    AND B.type = 0
    AND A.destination = c.stationid
    AND c.type = 0
    AND A.ticket_type = D.stationid
    AND D.type = 4
    AND A.journey_type = e.stationid
    AND e.type = 3

I have combined all the data in 1 table.

我把所有的数据都放在了一张桌子上。

This table just for storing name for station name and also ticket type.

此表仅用于存储站点名称和罚单类型。

because in table ticketcollections just store code (1, 2, 3, 4). So when I want to print ticket then I will refer to this station_tbl for display naming purpose.

因为在表ticketcollection中只存储代码(1、2、3、4)，所以当我想要打印罚单时，我就会引用这个“station_tbl”来显示命名目的。

Please help me if this query will make the query too slow.

如果这个查询会让查询太慢，请帮助我。

On my PC it is fast but on user's PC it is slow.

在我的电脑上它是快，但在用户的电脑上它是慢的。

I have refered 2 tables only.

我只提到了两张桌子。

2 个解决方案

#1

try this:

试试这个:

SELECT
    A.ticketid,
    B.station_name AS stationid,
    B.station_name AS destination,
    CONVERT(VARCHAR(5), GETDATE(), 108) AS TIME,
    CONVERT(VARCHAR(12), GETDATE(), 103) AS Date,
    B.station_name AS ticket_type,
    B.station_name AS journey_type,
    amount,
    issuedby
FROM ticketcollections A
    station_tbl AS B,
WHERE A.ticketidparent = '" + Request("ParentId") + "'
    AND A.stationid = B.stationid
    AND B.type in(0,4,3)

#2

do you have any indices on the tables? I would suggest putting them at least on stationId for ticketcollections and station_tbl and on station_tbl type.

你们有表上的指数吗?我建议至少把它们放在ticketcollections、station_tbl和station_tbl类型的station_tbl上。

#1